Comparing data same period last year

[CH_MJR79]

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 need to create a measure that shows the data as at the same period compared to last year.

The measure I have created so far that shows the year to date total is below

SickDaysYTD = CALCULATE(SUM(SickDays[Actual_Units]),DATESYTD(SickDays[Date],"11-30"))

(the "11-30" date is the from date, which is the end of our fiscal year)

I was wondering if anyone could suggest a way of creating a measure to calculate the total Actual Units for the same period one year ago.

I would need the measure to take into account the end of the fiscal year, so for example, we are at 2nd November now, so the comparative measure would need to select data from 30th November 2020 to 2nd November 2021.

I've put some sample data below. Thanks in advance.

Description	Date	Actual_Units
Training	25/10/2022	15.5
Training	13/10/2022	75
Training	12/10/2022	75
Training	11/10/2022	150
Training	10/10/2022	75
Training	07/10/2022	25
Training	03/10/2022	75
Training	28/09/2022	75
Training	26/09/2022	150
Training	23/09/2022	65
Training	20/09/2022	75
Training	15/09/2022	20
Training	12/09/2022	75
Training	07/09/2022	75
Training	06/09/2022	75
Training	01/09/2022	30
Training	24/08/2022	150
Training	23/08/2022	35
Training	19/08/2022	65
Training	18/08/2022	75
Training	17/08/2022	150
Training	16/08/2022	150

[CH_MJR79]

Many thanks @v-yanjiang-msft. I actually ended up doing this in a slightly different way, by modifying my underlying SQL query to get the data for the period I needed. But will try out your suggestion above.

[Satya_Bhamidipa]

Please check my solution 

Last Year = CALCULATE((Sum(Sheet1[Amount])),DATEADD(Sheet1[Date],-1,YEAR))

[v-yanjiang-msft]

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]

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 · Accepted solution]

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.