Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello Everyone,
I have two tables, one that is a clendar with dates/Month fields, and another that has sales info. Here is an example of the sales table:
Sales Date | Bill Amount
----------------------------------
12/1/2015 | 5
12/11/2015 | 10
1/1/2016 | 5
2/1/2016 | 12
6/1/2016 | 2
7/1/2016 | 4
1/1/2017 | 8
5/1/2017 | 10
6/1/2017 | 15
7/1/2017 | 2
What I need to do is create two measures that show running totals over renning 12 month period. So if today was 9/30/2017, I would need to show data like this:
Month/Year | Sales TY | Sales LY ---------------------------------------------- 10/16 | 0 | 5 11/16 | 0 | 0 12/16 | 0 | 10 1/17 | 8 | 5 2/17 | 0 | 12 3/17 | 0 | 0 4/17 | 0 | 0 5/17 | 10 | 0 6/17 | 15 | 2 7/17 | 2 | 4 8/17 | 0 | 0 9/17 | 0 | 0
I have tired using a calculate with datesbetween like this, but I can't seem to get it working when I use measures to calculate dates rather than hard coding dates:
Sales TY = CALCULATE(SUM('Bill Amount'), DATESBETWEEN( 'Calendar'[Date], "10/1/2016", "9/30/2016"), ALL('Calendar'[Date]))
Any and all helo us much appreicated!
Solved! Go to Solution.
In the sample you posted, the expected output isn't a running total, rather it's the sales for that month (not cumulative).
If that is the case you can create the following two calculated measures
Sales TY = SUM('Table1'[Bill Amount])
Sales LY = CALCULATE( SUM('Table1'[Bill Amount]), SAMEPERIODLASTYEAR('Dates'[Date]) )
and then drag them to a table along with a column for Month from your related date/calendar table.
Here is a link to download an example PBIX file
https://1drv.ms/u/s!AtDlC2rep7a-oEHaSI23CefHfxS6
In the sample you posted, the expected output isn't a running total, rather it's the sales for that month (not cumulative).
If that is the case you can create the following two calculated measures
Sales TY = SUM('Table1'[Bill Amount])
Sales LY = CALCULATE( SUM('Table1'[Bill Amount]), SAMEPERIODLASTYEAR('Dates'[Date]) )
and then drag them to a table along with a column for Month from your related date/calendar table.
Here is a link to download an example PBIX file
https://1drv.ms/u/s!AtDlC2rep7a-oEHaSI23CefHfxS6
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
95 | |
77 | |
65 | |
53 |
User | Count |
---|---|
144 | |
105 | |
102 | |
89 | |
63 |