Passing selected dates in slicer to a measure

[kkalyanrr]

Hello,

I'm using the following measure to calculative Cummlative Sum..

Cumulative Actual(ETS) = CALCULATE(CALCULATE(sum('Main Table'[OrderValue]),FILTER(ALL('Calendar'[Date].[Date]),'Calendar'[Date].[Date]<=MAX('Calendar'[Date].[Date]) )) , 'Calendar'[FiscalYear] = "FY17")

My requirement is, I'm using a Date slicer in the Dashboard UI and I'm looking for a measure that calculates the Cummulative Amount between the dates mentioned by User. Please help.

 

[v-yuezhe-msft]

@kkalyanrr,

Please change your DAX to  the following:

Cumulative Actual(ETS) = CALCULATE(CALCULATE(sum('Main Table'[OrderValue]),FILTER(ALLSELECTED('Calendar'[Date].[Date]),'Calendar'[Date].[Date]<=MAX('Calendar'[Date].[Date]) )) , 'Calendar'[FiscalYear] = "FY17")

Regards,
Lydia

[calerof]

Hi @v-yuezhe-msft ,

How do you create this measure for the same period last year?

Thanks a lot!

Fernando

[kkalyanrr]

Hello @v-yuezhe-msft ,

No matter what the starting date in the Slicer, this measure always sums the values from first date in the dataset to end date..

But I'm looking for a measure where when user gives a i/p date , it should sumup the values from that date to end date..

Thanks,

Kalyan..

[v-yuezhe-msft]

@kkalyanrr,

When you change ALL  function to ALLSELECTED function, the measure should change based on your date slicer. Would you please share sample data of your table so that I can test?

Regards,
Lydia

[kkalyanrr]

Hello @v-yuezhe-msft ,

Please find it here

https://drive.google.com/file/d/1ZqgzNnzyuOVQgbeUPH5si3Khc5Sd9OlW/view?usp=sharing 

 

[v-yuezhe-msft · Accepted solution]

@kkalyanrr,

Please check the modified PBIX file below.

https://1drv.ms/u/s!AhsotbnGu1Nok3Ll8CzEooAPooVx

Regards,
Lydia

[xyz123ABC]

The link doesn't seem to work. Do you mind reuploading the solution?

Thank you,

[v-yuezhe-msft]

@xyz123ABC ,

Please open a new thread describing your issue.

Regards,
Lydia