Forum Discussion

SpiroswayGR's avatar
SpiroswayGR
Resolver III
5 years ago

System Date / between filter for 2 different years

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

Need Help

8 Replies

  • iXpert_info's avatar
    iXpert_info
    Helper II
    5 years ago

    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's avatar
      SpiroswayGR
      Resolver III
      5 years ago

      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.

  • v-lionel-msft's avatar
    v-lionel-msft
    Community Support
    5 years ago

    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's avatar
      SpiroswayGR
      Resolver III
      5 years ago

      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's avatar
        v-lionel-msft
        Community Support
        5 years ago

        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.