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

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

 

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

    add 1 to the MONTH code like below to get Feb rather than Jan:

    month(DATEVALUE("2020/"&_M&"/01"))+1

     

    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,

       

      i did add one month in below measure:

       

      now below mentioned measure is not giving me any result

       

      any idea where is still the issue?

       

      Thanks

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

        Hi TaroGulati 

         

        Can you share your file here [after removing sensitive data]? And share a sample of your data in a table format here? And also, let me know the [total numbers] codes?

         

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

        Appreciate your Kudos !!