Forum Discussion

BrianNeedsHelp's avatar
BrianNeedsHelp
Resolver I
1 year ago
Solved

Calculation Enigma

I compute this year's daily goals based on last year/same month.  I get previous year like this- 364 days previous is the same weekday so it aligns properly.  

PYGA1=CALCULATE( 'Subscriber Activity'[Gross Adds],DATEADD('Calendar'[Calendar Date],-364,DAY)))

 Works great except then end of the month numbers are higher than they should be because it's calculating last year's first of the month on the last day of the month.  To correct it I did this: 

PYGA1 = IF('Calendar'[EndofMOnth]=DAY(SELECTEDVALUE('Calendar'[Calendar Date])),CALCULATE( 'Subscriber Activity'[Gross Adds],DATEADD('Calendar'[Calendar Date],-371,DAY)),CALCULATE( 'Subscriber Activity'[Gross Adds],DATEADD('Calendar'[Calendar Date],-364,DAY)))

It adjusts the last day of the month, but the total adds up to the same as it did when there was just the -364 measure.  So the PYALLGAs1 is showing the same total as it did in the first measure so it doesn't adjust each day higher like it should.  The goal for each day stays the same.  Not sure how this is even possible?  Any help greatly appreciated  

PYALLGAs1 = CALCULATE([PYGA1],

ALL('MyCalendar'[Date]))
GAGOAL = [PYGA1]/[PYALLGAs1]*[LocQuota1]

 So the essential problem is that the sum 15,457 is not correct.  If i export this and add it up it comes to 15,227.  

 

  • Tried parallel period but it put and astronomical number in each row. But I finally got the PYGA1 column to add up correctly by inputting another measure:  

    SUMX(SUMMARIZE(MyCalendar,MyCalendar[Date]),CALCULATE(SUMX(MyCalendar,[PYGA1])))

4 Replies

  • Deku's avatar
    Deku
    Super User

    Try using parallelperoid('Calendar'[Calendar Date], -1, year )

    • v-aatheeque's avatar
      v-aatheeque
      Community Support

      Hi BrianNeedsHelp ,

       

      Has your issue been resolved? If a community member's response addressed your query, please consider marking it as Accepted Answer and click Yes if you found it helpful.

      If you have any further questions, feel free to reach out.
      Thank you for being a valued member of the Microsoft Fabric Community Forum!

      • BrianNeedsHelp's avatar
        BrianNeedsHelp
        Resolver I

        Tried parallel period but it put and astronomical number in each row. But I finally got the PYGA1 column to add up correctly by inputting another measure:  

        SUMX(SUMMARIZE(MyCalendar,MyCalendar[Date]),CALCULATE(SUMX(MyCalendar,[PYGA1])))