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.    ...
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

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✌️!!

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

        Hi,

         

        Thank you for the idea. 

         

        my half issue is resolved. only issue is that outcome is of month that i am selecting not YTG months. for e.g with below mesaures if i am selecting Januray i am getting value of Jan not from Feb to Dec. 

        i created one measure the convert the month name

         

        i used above created mesaure in below measure:

         

        I am not able to understand the issue. 

         

        Thanks