Microsoft is giving away 50,000 FREE Microsoft Certification exam vouchers. Get Fabric certified for FREE! Learn more
Hi all,
I have a strange requirement. My dataset consists of orders. I am trying to calculate average MTD per ID for last 3 days.
I have calculated MTD Average per ID per month by. First I have calculated running total of the discount.
Rolling_Discount = VAR EarliestDate = CALCULATE(MIN('Sample'[Order Date]),ALLSELECTED()) RETURN CALCULATE(sum('Sample'[Discount%]),FILTER(ALL('Sample'[Order Date]),'Sample'[Order Date] <= MAX('Sample'[Order Date]) && 'Sample'[Order Date] >= EarliestDate))
Then calculated rolling count of the orders per day.
Cumulative_Count = VAR EarliestDate = CALCULATE(MIN('Sample'[Order Date]),ALLSELECTED()) RETURN CALCULATE(COUNT('Sample'[ordernumber]),FILTER(ALL('Sample'[Order Date]),'Sample'[Order Date] <= MAX('Sample'[Order Date]) && 'Sample'[Order Date] >= EarliestDate))
Then divided both to get MTD Average.
Mtd Test = DIVIDE([Rolling_Discount],[Cumulative_Count])
Everything is working fine.
But I want to show only last 3 days per month in the report. So if I filter date to top 3 on selected month my calculation is computing for the filtered last 3 days.
But what I want to show is the MTD averages from the start of the month, but in the report display only last 3 days of the month.
In the above image I have filtered last 3 days. Its calculating average for the filtered 3 days. But the result should be 47.62 , 47.22, 46.74 for the last 3 days.
Please help me with a solution.
PBIX LInk: https://www.dropbox.com/s/mtn2v0gr5il7kwx/MTD%20Test.pbix?dl=0
Solved! Go to Solution.
Hi @anil,
Please make some modofication to measures [Cumulative_Average] and [Cumulative_Count] as below:
Cumulative_Average = VAR EarliestDate = CALCULATE ( MIN ( 'Sample'[Order Date] ), ALL ( 'Sample'[Order Date] ) ) RETURN CALCULATE ( SUM ( 'Sample'[Discount%] ), FILTER ( ALL ( 'Sample'[Order Date] ), 'Sample'[Order Date] <= MAX ( 'Sample'[Order Date] ) && 'Sample'[Order Date] >= EarliestDate ) ) Cumulative_Count = VAR EarliestDate = CALCULATE ( MIN ( 'Sample'[Order Date] ), ALL ( 'Sample'[Order Date] ) ) RETURN CALCULATE ( COUNT ( 'Sample'[ordernumber] ), FILTER ( ALL ( 'Sample'[Order Date] ), 'Sample'[Order Date] <= MAX ( 'Sample'[Order Date] ) && 'Sample'[Order Date] >= EarliestDate ) )
Best regards,
Yuliana Gu
Hi @anil,
Please make some modofication to measures [Cumulative_Average] and [Cumulative_Count] as below:
Cumulative_Average = VAR EarliestDate = CALCULATE ( MIN ( 'Sample'[Order Date] ), ALL ( 'Sample'[Order Date] ) ) RETURN CALCULATE ( SUM ( 'Sample'[Discount%] ), FILTER ( ALL ( 'Sample'[Order Date] ), 'Sample'[Order Date] <= MAX ( 'Sample'[Order Date] ) && 'Sample'[Order Date] >= EarliestDate ) ) Cumulative_Count = VAR EarliestDate = CALCULATE ( MIN ( 'Sample'[Order Date] ), ALL ( 'Sample'[Order Date] ) ) RETURN CALCULATE ( COUNT ( 'Sample'[ordernumber] ), FILTER ( ALL ( 'Sample'[Order Date] ), 'Sample'[Order Date] <= MAX ( 'Sample'[Order Date] ) && 'Sample'[Order Date] >= EarliestDate ) )
Best regards,
Yuliana Gu
Thanks for the reply Yuliana Gu,
It worked like a charm.
Please help me with a solution.
Check out the April 2025 Power BI update to learn about new features.
Explore and share Fabric Notebooks to boost Power BI insights in the new community notebooks gallery.
User | Count |
---|---|
97 | |
60 | |
47 | |
35 | |
34 |