Daily total between two dates

[Anonymous]

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 date, it is the true spend for the two days.

I am OK with getting a daily cost for a single day but how would I get the daily cost for two separate days and get the data in the same table? I would really like this solution to involve a date picker so the user can select any day they want.

The category is subscriptions in Azure.

What other information do you need?

[amitchandak]

@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]

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 · Accepted solution]

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]

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. 

[Greg_Deckler]

Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[Anonymous]

Thank you for the advice; I have tried to match your request and please let me know additional requests.

1. Power BI Usage_Details Table

Subscription_Name	Cost	Year	Month	Day
Name_1	2.24643651	2020	May	1
Name_2	32.3810413	2020	May	1
Name_3	20.0792305	2020	May	1
Name_4	4.84115459	2020	May	1

2. Desired Result

SubscriptionName	Cost	Year	Month	Day	Cost_2	Year_2	Month_2	Day_2
Name_1	2.246436511	2020	May	1	2.24643651	2020	May	17
Name_2	32.23771044	2020	May	1	32.3810413	2020	May	17
Name_3	20.09001796	2020	May	1	20.0792305	2020	May	17
Name_4	4.881155111	2020	May	1	4.84115459	2020	May	17

3. I would like the flexibility of selecting any two dates. I do not want, for example, to select the first date and then a relative second date (i.e., 1 week before, 1 week after).