Running Totals with one date slicer

[Anonymous]

Hi Guys, 

I trust everyone is doing well.

I have an issue with Running totals:

Problem

I am currently building a report for a client where it displays sales quantity( Trans_sales table) and Running totals(Item ledger entry table), these two tables have their own date columns, I am in a situation where two date slicers need to be on the report in order to get the correct amount since running totals start date is the beginning of time and sales quantity is for a specific period.

Question

I am wondering if there is a way to have one date slicer to get the amounts for both, i think the solution i am looking has to do with the running total ignoring the start date that is selected, meaning it always takes the beginning of time date, while sales will take in account both the start and end date.

Assistance will be much appreciated

Kind Regards

Bash

[Anonymous]

@Stachu has given you a piece of excellent advice. You should follow it. All I can add is that you should (almost) never use a date column from a table that's not a proper Date table. For many more reasons that I have time and space to enlarge upon but one of them being the fact that then and only then will you be able to use time-intelligence functions. Whatever you do, make sure you do it well and according to Best Practices. Otherwise, YOU'LL BE SORRY rather sooner than later.

Best

Darek

[Anonymous]

Hi @Anonymous,

Thank you for taking the time out to respond. I agree with your statement, I am still new to power bi and all you guys assistance helps me alot. I will definately keep the best practices in mind 🙂

I still have issues though, please have a look at reply I gave @Stachu. 

If you have any ideas that I could try please dont hesitate in letting me know, open to try any new approaches.

Kind Regards

Bash

[Anonymous · Accepted solution]

When your table has multiple date columns, then you've got 2 main choices:

1. You create one Date table and join it to the columns. Only one relationship will be active. For measures that want to use the other dates, you'll be using USERELATIONSHIP within CALCULATE.

2. You create as many Date tables as there are columns with dates and join each to the respective column. Of course, each such a Date table should be adjusted to the type of date it's serving. For order dates, your order years should be named something like "OY 2019", and shipping years should be named like "SY 2019." Basically, you should do something that will enable an easy identification of which calendar you're talking about.

Both solutions have their uses, cons and pros. For instance, if you have separate calendars, you can easily create cross-tabulations. If you have one Date table, you can easily create charts comparing different measures (that use different calendars) side by side. Really, which solution you choose depends on your requirements.

Whatever you choose, you can do ANYTHING in a measure. I mean ANYTHING. So, if you need a measure that will be showing you the total from the beginning of (your) time, you can write it. It's actually very simple. Something like:

var __maxDate = MAX ( 'Calendar'[Date] )
var __value =
	CALCULATE(
		[My Measure],
		'Calendar'[Dates] <= __maxDate 
	)
RETURN
	__value

Well, does this help?

Best

Darek

[Anonymous]

Hi @Anonymous,

ignore the previous reply, found my error in the formula. This has solved the issue for me. This is the correct formula that i was suppose to use.

Running Totals = var __maxDate = MAX ( Item Ledger Entry'[Posting Date])

var __Value =

    CALCULATE(

        [SOH Running Total],

        [Posting Date]<= __maxDate

        )   Return __Value

Thanks to you and @Stachu for all the assistance. 

Kind Regards

Bash

[Anonymous]

Your formula may be working but IT'S A BAD FORMULA.

Please save yourself trouble in the future and follow this simple rule:

A measure MUST NOT ever be preceded by the name of the table it belongs to.

A column name MUST ALWAYS be preceded by the name of the table it belongs to.

If you don't adhere to this, you'll be sorry one day.

Best

Darek

[Anonymous]

Hi @Anonymous ,

Thanks for the advise:) 

My formula meets your rules though,

A column name MUST ALWAYS be preceded by the name of the table it belongs to:

'Item Ledger Entry '[Posting Date]

&

A measure MUST NOT ever be preceded by the name of the table it belongs to:

SOH Running Total (This comes from my item ledger entry table)

I appreciate the guidance though 🙂

Kind Regards

Bash

[Anonymous]

Not really 🙂

This is what you've posted before:

Running Totals = var __maxDate = MAX ( Item Ledger Entry'[Posting Date])
var __Value =
    CALCULATE(
        [SOH Running Total],
        [Posting Date]<= __maxDate
        )   Return __Value

You'll notice that [Posting Date] is the offender.

Best

Darek

[Anonymous]

Hi @Anonymous,

Apologies lol 🙂

I edited that formula when pasting it in the reply due to clients information being in it and must have deleted the table name before posting date.

The correct formula is :

Running Totals = var __maxDate = MAX ( Item Ledger Entry'[Posting Date])
var __Value =
    CALCULATE(
        [SOH Running Total],
      'Item Ledger Entry'[Posting Date]<= __maxDate
        )   Return __Value

Thanks for all the assitance @Anonymous, I truly appreciate it 🙂

Kind Regards

[Anonymous]

Format your DAX properly: www.daxformatter.com. Please don't write sloppy code.

Best

Darek

[Anonymous]

Hi @Anonymous,

Thanks for the prompt response.

What if one measure needs two use the two dates?

I have named the dates accordingly, thanks for that.

I am trying to create that formula you gave however I am getting an error "unexpected expression.  This is my formula that gives me an error : 

var __maxDate = MAX ( [Posting Date])

var __Value =

    CALCULATE(

        [SOH Running Total],

        [Posting Date]<= MAX Item Ledger Entry'[Posting Date]

        )   Return __Value

Am I missing something?

Kind Regards

Bash

[Stachu]

1) create a new table just for the Calendar (in Power Query or with DAX e.g. using CALENDAR or CALENDARAUTO)

2) mark it as a Date table (as described here https://docs.microsoft.com/en-us/power-bi/desktop-date-tables)

3) create joins to your data tables

4) create a slicer based on the calendar table

5) adjust the DAX in your measures to use Calendar[Date] rather than date columns in the data tables

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

[Anonymous]

Hi @Stachu,

Thank you for getting back to me and providing me with the information:) However that does not fix my issues:(

Problem 1:

I have tried creating my own date table but it didnt seem to solve the issue I was facing.

The issue i had was the fact tabe makes use of two dates, when creating my own date table, two relationships would need to be made. One of the relationships will be inactive, i then tried using the userrelationship but then realised that it would look at the relationship that is in the formula (Remind you that i do need to use two dates not one).

Solution 1:

I came up with a solution by creating two date slicers from the two columns and then grouping and syncing them. After which I then hide one of them so that one date slicer is used. This seems to work but I am still left with another problem. lol

Problem 2:

This issue I cannot seem to solve without having two date slicers, one date slicer needs to be on the before setting (This is for the running totals since it doesnt need the start date), while the other needs to be on the between date setting (This is for the sales quantity for a specific period of time). I cannot use the between date slicer only due to the effect its having on my running total, i wanted to know if there is a dax formula that will ignore the start date when using between date setting ? 

Thanks in Advance:)

Kind Regards

Bash