Forum Discussion

CH_MJR79's avatar
CH_MJR79
Helper II
3 years ago
Solved

Comparing data same period last year

Hi all,   I have table that contains lines posted to a timesheet each day for certain categories. I've created a measure that calculates the total 'Actual Units' for the year to date, but I also n...
Need Help
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    3 years ago

    Hi CH_MJR79 ,

    According to your description, I modify the formula:

    SickDaysYTD_Pre =
VAR _Pre =
    DATEADD ( 'SickDays'[Date], -12, MONTH )
VAR _PreToday =
    DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
RETURN
    CALCULATE (
        SUM ( SickDays[Actual_Units] ),
        DATESYTD ( _Pre, "11-30" ),
        'SickDays'[Date] <= _PreToday
    )

    Get the correct result in my sample.

    I attach my sample below for your reference.

     

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yanjiang-msft's avatar
v-yanjiang-msft
Community Support
3 years ago

Hi CH_MJR79 ,

According to your description, here's my solution, create a measure.

SickDaysYTD_Pre =
VAR _Pre =
    DATEADD ( 'SickDays'[Date], -12, MONTH )
RETURN
    CALCULATE ( SUM ( SickDays[Actual_Units] ), DATESYTD ( _Pre, "11-30" ) )

Get the correct result in my sample:

I attach my sample below for your reference.

 

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • CH_MJR79's avatar
    CH_MJR79
    Helper II
    3 years ago

    Hi v-yanjiang-msft 

    I've done some more work on this and I can see what is happening - the YTD_Pre formula you kindly provided seems be adding up the total for the whole of the previous fiscal year (i.e. 1st November 2020 to 30th November 2021), rather than calculating the total as at the date that the report is being ran, i.e. if I run the report today (3rd November 2022) then it should show the previous year total as at 3rd November 2021.

     

    The forum doesn't allow me to attach a copy of the Power BI file I'm playing with but I have attached screenshot.

     

    • v-yanjiang-msft's avatar
      v-yanjiang-msft
      Community Support
      3 years ago

      Hi CH_MJR79 ,

      According to your description, I modify the formula:

      SickDaysYTD_Pre =
VAR _Pre =
    DATEADD ( 'SickDays'[Date], -12, MONTH )
VAR _PreToday =
    DATE ( YEAR ( TODAY () ) - 1, MONTH ( TODAY () ), DAY ( TODAY () ) )
RETURN
    CALCULATE (
        SUM ( SickDays[Actual_Units] ),
        DATESYTD ( _Pre, "11-30" ),
        'SickDays'[Date] <= _PreToday
    )

      Get the correct result in my sample.

      I attach my sample below for your reference.

       

      Best Regards,
      Community Support Team _ kalyj

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.