Forum Discussion

TaroGulati's avatar
TaroGulati
Icon for Helper III rankHelper III
5 years ago
Solved

Year to go Measure

Hello guys,

 

I am trying to create the YTG measure using month name and year. for e.g, if i select January and 2021 in filter, i am expecting the outcome from Feburary 2021 to December 2021. 

 

 

I restricted to use the month name in filter insted of month number. 

 

i will appreciate any suggestions. 

 

Thanks

Need Help
  • v-luwang-msft's avatar
    v-luwang-msft
    5 years ago

    Hi  TaroGulati ，

    Try like below :

    step1, create silcer table :

    Table2 = DISTINCT('Table'[YYYYMM])
    MON = RIGHT(Table2[YYYYMM],2)
    year = LEFT(Table2[YYYYMM],4)

    Step 2，create month and year column on base table:

    Month = FORMAT('Table'[Date],"MM")
    Year = FORMAT('Table'[Date],"YYYY")

     Then use the below measure :

    NUMBER = 
VAR MON =
    CALCULATE (
        MAX ( 'Table2'[MON] ),
        FILTER ( ( 'Table2' ), MAX ( 'Table'[Month] ) > SELECTEDVALUE ( Table2[MON] ) )
    )
VAR YEAR =
    CALCULATE (
        MAX ( 'Table2'[year] ),
        FILTER ( ( 'Table2' ), MAX ( 'Table'[Year] ) = SELECTEDVALUE ( Table2[year] ) )
    )
RETURN
    IF ( MON <> BLANK () && YEAR <> BLANK (), MAX ( 'Table'[Numbers] ), BLANK () )

    Get the below:

     

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien

9 Replies

  • VahidDM's avatar
    VahidDM
    Icon for Super User rankSuper User
    5 years ago

    Hi TaroGulati 

     

    To use the DATE code, convert the month name to number.

     

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

    Appreciate your Kudos✌️ !!

     

    • TaroGulati's avatar
      TaroGulati
      Icon for Helper III rankHelper III
      5 years ago

      Hi,

       

      Thank you for your response. 

       

      i am ristricted to use Month name in filter option. inside mesaure i need to mentioned selcted value of month name. do you know any function that can help to keep month name in filter but mesaure provide result based on month number?

       

      Thanks

      • VahidDM's avatar
        VahidDM
        Icon for Super User rankSuper User
        5 years ago

        You can use your month name, so try to use/add this measure/code in your code to find the month number:

         

         

         

         

        Measure = 
var _M = SELECTEDVALUE('Sheet1'[Month])
return
month(DATEVALUE("2020/"&_M&"/01"))

         

         

         

         

         

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

        Appreciate your Kudos✌️!!

  • v-luwang-msft's avatar
    v-luwang-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi  TaroGulati ，

    Try like below :

    step1, create silcer table :

    Table2 = DISTINCT('Table'[YYYYMM])
    MON = RIGHT(Table2[YYYYMM],2)
    year = LEFT(Table2[YYYYMM],4)

    Step 2，create month and year column on base table:

    Month = FORMAT('Table'[Date],"MM")
    Year = FORMAT('Table'[Date],"YYYY")

     Then use the below measure :

    NUMBER = 
VAR MON =
    CALCULATE (
        MAX ( 'Table2'[MON] ),
        FILTER ( ( 'Table2' ), MAX ( 'Table'[Month] ) > SELECTEDVALUE ( Table2[MON] ) )
    )
VAR YEAR =
    CALCULATE (
        MAX ( 'Table2'[year] ),
        FILTER ( ( 'Table2' ), MAX ( 'Table'[Year] ) = SELECTEDVALUE ( Table2[year] ) )
    )
RETURN
    IF ( MON <> BLANK () && YEAR <> BLANK (), MAX ( 'Table'[Numbers] ), BLANK () )

    Get the below:

     

     

    Did I answer your question? Mark my post as a solution!


    Best Regards

    Lucien