Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Daily total between two dates

Hi everyone - Interesting use case has come up. As a user, I want to select two dates, so that I can see the Daily Total Spend for each date on the same row. This is not the running total for the da...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    hi  Anonymous 

    For your case, you could use a matrix visual to get it.

    Just drag date field into columns of matrix visual, and for slicer, if you just want to choose two date, you'd better use list for this slicer.

     

    and if you want use range of a slicer(Between), you could create a measure as below to get it:

    Measure = 
var ascdaterank =RANKX(ALLSELECTED('Table'[Date]),CALCULATE(MAX('Table'[Date])),,ASC)
var descdaterank =RANKX(ALLSELECTED('Table'[Date]),CALCULATE(MAX('Table'[Date])),,DESC)
return
IF(ascdaterank=1||descdaterank=1,CALCULATE(SUM('Table'[Cost])))

     

    Result:

     

    and here is sample pbix file, please try it.

     

    Regards,

    Lin

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

hi  Anonymous 

For your case, you could use a matrix visual to get it.

Just drag date field into columns of matrix visual, and for slicer, if you just want to choose two date, you'd better use list for this slicer.

 

and if you want use range of a slicer(Between), you could create a measure as below to get it:

Measure = 
var ascdaterank =RANKX(ALLSELECTED('Table'[Date]),CALCULATE(MAX('Table'[Date])),,ASC)
var descdaterank =RANKX(ALLSELECTED('Table'[Date]),CALCULATE(MAX('Table'[Date])),,DESC)
return
IF(ascdaterank=1||descdaterank=1,CALCULATE(SUM('Table'[Cost])))

 

Result:

 

and here is sample pbix file, please try it.

 

Regards,

Lin

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi Lin - That is a legitimate solution, thank you so much. Works exactly as expected. It did not occur to me to use the date as a dropdown; sometimes it takes a fresh set of eyes. Stay safe and healthy.