Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
codyraptor
Resolver I
Resolver I

Defer Dates...divide based on date..and sum the results

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.

 

6 REPLIES 6
v-yalanwu-msft
Community Support
Community Support

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.

v-yalanwu-msft
Community Support
Community Support

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

codyraptor
Resolver I
Resolver I

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?

amitchandak
Super User
Super User

@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.
MeasurePic.JPG

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors