Number of hours from last year

[Shakaze]

Hello everyone,

I have some issues about dates in my report.

I have datas about how many hours workers have done on a period of time (month/year).

My goal is to create a graphic showing the amount of hours done on the year XXXX and compare this with last year (XXXX-1).

I have a slicer with only year (2018-2022)

For now, i have only the number of the year I have selected and i want to show the previous year however i'm struggling to create this mesure.

For the dates I have 3 colums -> (dd/mm/yy) in date format, months in in integer and year in integer too.

At the moment my slicer is selected on the 3rd one (year which is an integer).

What I really want is to show the previous year's datas which I have selected on my slicer (so If i select the year 2022 on my slicer I wanna show the year 2021 on a curve in my graphic).

If anyone has an idea!

Thank you,

[amitchandak · Accepted solution]

@Shakaze , Join your date with date table, On one join can be active.

Measure example for active join

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

This month = CALCULATE([Net],DATESMTD(ENDOFMONTH('Date'[Date])))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))


last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
Previous year Month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth(dateadd('Date'[Date],-11,MONTH)))

Example for inactive joins

calculate( calculate( SUM(Sales[Sales Amount]),USERELATIONSHIP ('Sales'[CreateDate], 'Date'[Date])),DATESMTD('Date'[Date]))

calculate( calculate( SUM(Sales[Sales Amount]),USERELATIONSHIP ('Sales'[CreateDate], 'Date'[Date])),SAMEPERIODLASTYEAR('Date'[Date]) )

https://radacad.com/userelationship-or-role-playing-dimension-dealing-with-inactive-relationships-in...