cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
kkalyanrr Member
Member

Passing selected dates in slicer to a measure

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.

 

 KYC.JPG

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Passing selected dates in slicer to a measure

@kkalyanrr,

Please check the modified PBIX file below.

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
8 REPLIES 8
Moderator v-yuezhe-msft
Moderator

Re: Passing selected dates in slicer to a measure

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kkalyanrr Member
Member

Re: Passing selected dates in slicer to a measure

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..

Moderator v-yuezhe-msft
Moderator

Re: Passing selected dates in slicer to a measure

@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

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kkalyanrr Member
Member

Re: Passing selected dates in slicer to a measure

Moderator v-yuezhe-msft
Moderator

Re: Passing selected dates in slicer to a measure

@kkalyanrr,

Please check the modified PBIX file below.

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

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
xyz123ABC Frequent Visitor
Frequent Visitor

Re: Passing selected dates in slicer to a measure

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

Thank you,

Moderator v-yuezhe-msft
Moderator

Re: Passing selected dates in slicer to a measure

@xyz123ABC ,

Please open a new thread describing your issue.

Regards,
Lydia

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
calerof Member
Member

Re: Passing selected dates in slicer to a measure

Hi @v-yuezhe-msft ,

 

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

 

Thanks a lot!

 

Fernando