Forum Discussion

Puja's avatar
Puja
Helper III
3 years ago
Solved

Need guidance

Hello all , 

 

Please give me some guidence on how to get previous year month dates.

i have a date table with Rank like  below 

 

 

If I select 12/3/2022, the result should be rank 1 

 

If I select 12/31/2022 then the result should be Rank 2 and Rank 1 of previous years since rank 2 is not available in previous years.

ex:

DecDec-2012/5/20201
DecDec-2112/4/2021 1
DecDec-2212/31/2022 2

 

 

 

TIA

 

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Puja ,

    Please refer to my pbix file.

    Measure =
VAR _1 =
    SELECTEDVALUE ( data[Date] )
VAR _format =
    FORMAT ( _1, "MMM" )
VAR _YEAR =
    YEAR ( _1 )
RETURN
    IF (
        _1 = SELECTEDVALUE ( 'Table'[Date] ),
        1,
        IF (
            _format = SELECTEDVALUE ( 'Table'[ Month] )
                && _YEAR <> SELECTEDVALUE ( 'Table'[year_colun] ),
            1
        )
    )

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

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

4 Replies

  • andhiii079845's avatar
    andhiii079845
    Solution Sage
    3 years ago

    I do not understand your filter logic. Do you want to compare your current seleted month with the privious year (same month)?
    Also I need some data example no screenshot. If rank is calculated also the DAX definition. 

    • Puja's avatar
      Puja
      Helper III
      3 years ago

      andhiii079845 

      The goal is compare current year Date with previous year  Date (not Month vs Month)

      As the dates are not consistent , I added rank (this is optional ) 
      Here is the test DATA.       The eamples were added in my first request.

      Date Month YearRank
      8/14/2021AugAug-211
      8/28/2021AugAug-212
      8/20/2022AugAug-221
      12/5/2020DecDec-201
      12/4/2021DecDec-211
      12/3/2022DecDec-221
      12/31/2022DecDec-222
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi Puja ,

    Please refer to my pbix file.

    Measure =
VAR _1 =
    SELECTEDVALUE ( data[Date] )
VAR _format =
    FORMAT ( _1, "MMM" )
VAR _YEAR =
    YEAR ( _1 )
RETURN
    IF (
        _1 = SELECTEDVALUE ( 'Table'[Date] ),
        1,
        IF (
            _format = SELECTEDVALUE ( 'Table'[ Month] )
                && _YEAR <> SELECTEDVALUE ( 'Table'[year_colun] ),
            1
        )
    )

    How to Get Your Question Answered Quickly 

     

    If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

     

    Best Regards
    Community Support Team _ Polly

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

    • Puja's avatar
      Puja
      Helper III
      3 years ago

      Hi Anonymous , 

      Thank you for your time. Your solution working for some dates only. In this example if user choose 12/4/2021, then same date and previous year date.

      Another example.

       

      Much appriciated for your help