Forum Discussion
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 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?
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
6 Replies
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.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).
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.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
