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

amitchandak's avatar
amitchandak
Super User
6 years ago

Anonymous , if take a date and put into a slicer, you have range option with a down symbol.

Same way if you put date in table or row of a matrix or on the axis of visual you can see day-wise data.

 

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks - The range of a slicer is a little off; we need to compare the values associated with two dates without worrying about what falls in between them.

 

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