System Date / between filter for 2 different years

[SpiroswayGR]

Dear all,

I am trying to create 2 measures to calculate revenue for 2 different years.

1st : CY Revenue  = CALCULATE(SUMX (revenue(field) , filters ) this is correct

2nd : PLY (previews last year = calculate (CY revenue ,dateadd(date field , -2 , year) this is correct.

The date field is calendar field that connect my main table.

I have also a system date field that show the pc date that any data imported.

My target is to calculate both revenues but when i filter system date 01/01/2021 to 10/01/2021 for CY) then i want PLY to filtered with same system date for PLY (that means 01/01/2019 - 10/01/2019).

Any idea ? 

Thank you in advance

[v-lionel-msft]

Hi @SpiroswayGR ,

Is your data model like this?

Based on this data model, when you filter the [System Date] field in the slicer, the date field in the calendar table is not changed, so the filter context in the measure formulas are not changed.

So you should add the date field of the calendar table to the slicer.

Best regards,
Lionel Chen

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

[SpiroswayGR]

@v-lionel-msft thank you reply.

Well , model is almost same but with the difference of Sheet1.

Sheet1 should have Date  that is connected with Calendar Date. Also System Date in Sheet1 that is Entry Date that information imported to the system.

Maybe should i change connection calendar date to sheet1 system date to work ? 

[v-lionel-msft]

Hi @SpiroswayGR ,

Is your model like this?

You should add the dates in the fact table (Calendar table) to the slicer.

Best regards,
Lionel Chen

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

[SpiroswayGR]

like this

Sheet1 = main table

[v-lionel-msft]

Hi @SpiroswayGR ,

The first method is to use the date field of the calendar table in the slicer, and the second method is to use the date field of the main table in the DAX formula.

Best regards,
Lionel Chen

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

[SpiroswayGR]

any update on that ?

Thank you in advance

[iXpert_info]

Hey @SpiroswayGR 

If you have a Date Dimension table then just try below measure for PLY
PLY (previews last year = calculate (CY revenue ,date[Year]=year(today())-2 )

This seems to be working in my case where I had the similar scenario.

Did I answer your question? If yes please mark this as solution

Best Regards
Jay Patel
iXpert Analytics

[SpiroswayGR]

@iXpert_info 

I have the same metrics but is not working.

The problem is on "system date" filter.

(Working with Hotel Data)

I have 2 dates : 1st calendar date that connect with hotel date.

2nd System date that is the pc date that one reservation imported.

I am calculating revenue with that way for CY and PLY correctly but when i filter / slicer with system date working only for CY. 

For example : I want to filter system date only january 2021 and i want my PLY calculation to filrted with same filter but for january of 2019.