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.
Before I starte waisting hours on finding the best practise on this topic, I though I might just ask given the question is pretty simple.
Let's say you have a model with the following data:
Customer | Monthlyly fee | Start Date | End Data
A | 100 | 01/06/2019 | 30/11/2019
B | 125 | 01/06/2019 | 31/12/2019
C | 75 | | 01/06/2019 | 31/10/2019
How can I get the monthly total (sum) values in a table to analyze this? Do you generate the monthly values first in PowerQuery of can this be done on the fly using DAX?
Solved! Go to Solution.
Hi @wlknsn ,
In your scenario, I would use the following DAX query to do that:
Result = CALCULATE ( SUM ( Data[Monthly fee] ), FILTER ( ALL ( Data ), Data[Start Date] <= MIN ( 'Date'[Date] ) && Data[End Date] >= MAX ( 'Date'[Date] ) ) )
The result will like below:
Best Regards,
Teige
Hi @wlknsn ,
In your scenario, I would use the following DAX query to do that:
Result = CALCULATE ( SUM ( Data[Monthly fee] ), FILTER ( ALL ( Data ), Data[Start Date] <= MIN ( 'Date'[Date] ) && Data[End Date] >= MAX ( 'Date'[Date] ) ) )
The result will like below:
Best Regards,
Teige
Hi @wlknsn
No need to pre aggregate data if your table is not massive, I would try DAX and if performance is not satisfactory then you can consider aggregating yuor data.
Regards,
Mariusz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Is there an easy way to do convert that data into monthly records in powerquery?
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 |
---|---|
115 | |
100 | |
88 | |
69 | |
61 |
User | Count |
---|---|
152 | |
120 | |
102 | |
87 | |
68 |