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 

 

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