Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a very specific calculation and I'm hoping to use 'calculated groups' to maybe optimize the performance. I have to defer sales date by 12 months...the 1st and last month are then divided by 24 and everything in between is divided by 12. I then 'sum' the data by the deferred months.
I have this working, but I'm using an if/then statement with division to get the 24/12 part...and this is not giving me the correct total columns. I feel like I'm almost there, but can't seem to get the total correct.
Below is the dax:
Var NumOfMonths = 12
Var ReferenceDate = SELECTEDVALUE('DeferDatet'[Defer Date])
Var Referencemonth = MONTH(ReferenceDate)
Var ReferenceSalesMonth = SELECTEDVALUE('SalesDate'[Month num])
Var SpreadDates =
DATESINPERIOD(
'SalesDate'[Sales Date],
ReferenceDate,
NumOfMonths,
MONTH
)
Var Calc =
CALCULATE(
'Sales Measures'[Sales Count],
REMOVEFILTERS('DeferDate'),
KEEPFILTERS( SpreadDates),
USERELATIONSHIP('DeferDate'[Defer Date],'SalesDate'[Sales Date]))
Var Result = if(Referencemonth = ReferenceAcctMonth,divide(Calc,24),DIVIDE(Calc,12))
return
I can provide a sample pbix if needed.
Hi, @codyraptor ;
This onedrive does not have permission. You can change it.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @codyraptor ;
Could you provide a simple .pbix and expected output? It makes it easier to give you a solution.
Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-yalanwu-msft absolutely. I have a simple pbix that includes the expected outcome. How do I upload that here to share? Will this onedrive link work? Defer Calc Sample.pbix
I've been able to get the correct answer by creating a new separate measure...using the following dax. "SUMX( Sales Date, "Result from previous measure"). The answer is perfect. The next step is to put this in a calculated group so i can use multiple measures against the same logic. When I plug the original logic in...I get the correct calculated group...but I'm back to not being able to use SUMX to get the proper sum. 😞 Thoughts? Anyone ever create a SUMX against results of a calculated group?
@codyraptor , Try like
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-11),-1,MONTH)) /24 + CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),-1),-10,MONTH))/12 + CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],eomonth(MAX('Date'[Date ]),0),-1,MONTH))/24
Thank you for the response. Unfortunately, this doesn't give me what I need. I'm getting the same result in the column/rows...but the total is not accurate. You can see the 2 results below...the 'expected' was done manually in excel.
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
86 | |
54 | |
54 | |
38 | |
21 |