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.
I have a fact table with a Quantity measure that has some quantities for every day up to yesterday.
I need to have a line chart that tracks current and previous months Quantity by day for beginning of month through yesterday. (Example below) When I use the MTD calculation, it is a running total rather than the actual quantity per day.
What's the best way to go about this? Calculated measures for current month (Nov 1-12) and prior month (Oct 1-12)? How would I write those?
Sample data below:
My data - all I want returned is what's in the chart above (created in Excel, need to recreate similar in power bi). So I only care about 10/1-10/12 and 11/1-11/12 data. I still need it broken up by day, not the full MTD total.
factSales table | |
Date | Quantity |
10/1/2018 | 17696 |
10/2/2018 | 31072 |
10/3/2018 | 26309 |
10/4/2018 | 25934 |
10/5/2018 | 34095 |
10/6/2018 | 17907 |
10/7/2018 | 18546 |
10/8/2018 | 21664 |
10/9/2018 | 25757 |
10/10/2018 | 22143 |
10/11/2018 | 23938 |
10/12/2018 | 32196 |
10/13/2018 | 22000 |
10/14/2018 | 31512 |
10/15/2018 | 15885 |
10/16/2018 | 5643 |
10/17/2018 | 9100 |
10/18/2018 | 20000 |
10/19/2018 | 25000 |
10/20/2018 | 21000 |
10/21/2018 | 35455 |
10/22/2018 | 21002 |
10/23/2018 | 21004 |
10/24/2018 | 36985 |
10/25/2018 | 75412 |
10/26/2018 | 11112 |
10/27/2018 | 11587 |
10/28/2018 | 14568 |
10/29/2018 | 24556 |
10/30/2018 | 24587 |
10/31/2018 | 23510 |
11/1/2018 | 33296 |
11/2/2018 | 16544 |
11/3/2018 | 18232 |
11/4/2018 | 16015 |
11/5/2018 | 14005 |
11/6/2018 | 14200 |
11/7/2018 | 19414 |
11/8/2018 | 19252 |
11/9/2018 | 21209 |
11/10/2018 | 18874 |
11/11/2018 | 19697 |
11/12/2018 | 18051 |
Solved! Go to Solution.
Hi @Anonymous,
For your requirement, you could try the two measures below.
Oct = VAR a = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) - 1 ) VAR b = STARTOFMONTH ( 'Table1'[Date] ) RETURN CALCULATE ( SUM ( Table1[Quantity] ), DATESBETWEEN ( 'Table1'[Date], b, a ), FILTER ( 'Table1', MONTH ( 'Table1'[Date] ) = MONTH ( TODAY () ) - 1 ) ) Nov = CALCULATE ( SUM ( Table1[Quantity] ), DATESBETWEEN ( 'Table1'[Date], STARTOFMONTH ( 'Table1'[Date] ), TODAY () - 1 ), FILTER ( 'Table1', MONTH ( 'Table1'[Date] ) = MONTH ( TODAY () ) ) )
Then you could create a calculated column with Day() function and use Day column as Axis and use Oct, Nov as values to create the line chart like below.
More details, you could refer to my attachment.
Best Regards,
Cherry
Possible to add some sample data? Posts with that generally get more answers.
More details added above. It's essentially a Month over Month comparison by day. I need to limit the amounts on the chart to Oct 1-12 and Nov 1-12 from my Quantity measure.
I think this works for current month: CALCULATE(SUM(factSalesTPD[QuantityShipped]),filter('Date','Date'[Date]<=today()-1 && 'Date'[Date]>=DATE(YEAR(TODAY()-1),MONTH(TODAY()-1),1)))
But I'm not sure how to do previous month. And what happens when we're at the end of the month and I can't compare the 31st to 30th? Will it just be blank or error out?
Hi @Anonymous,
For your requirement, you could try the two measures below.
Oct = VAR a = DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) - 1 ) VAR b = STARTOFMONTH ( 'Table1'[Date] ) RETURN CALCULATE ( SUM ( Table1[Quantity] ), DATESBETWEEN ( 'Table1'[Date], b, a ), FILTER ( 'Table1', MONTH ( 'Table1'[Date] ) = MONTH ( TODAY () ) - 1 ) ) Nov = CALCULATE ( SUM ( Table1[Quantity] ), DATESBETWEEN ( 'Table1'[Date], STARTOFMONTH ( 'Table1'[Date] ), TODAY () - 1 ), FILTER ( 'Table1', MONTH ( 'Table1'[Date] ) = MONTH ( TODAY () ) ) )
Then you could create a calculated column with Day() function and use Day column as Axis and use Oct, Nov as values to create the line chart like below.
More details, you could refer to my attachment.
Best Regards,
Cherry
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |