Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

YTD calculation using Month name

Hi Team,   I have to calculate YTD using month name    Please refer the below screenshot for your referene. Is it possible to do YTD without unpivot of these columns?I want to calculate YTD ...
Need Help
  • v-chuncz-msft's avatar
    v-chuncz-msft
    6 years ago

    Anonymous 

     

    You may try the measure below.

    Measure =
VAR m =
    MAX ( 'Calendar'[Date].[MonthNo] )
RETURN
    SUMX (
        Table1,
        Table1[Jan]
            + Table1[Feb]
                * IF ( 2 <= m, 1, 0 )
            + Table1[Mar]
                * IF ( 3 <= m, 1, 0 )
            + Table1[Apr]
                * IF ( 4 <= m, 1, 0 )
            + Table1[May]
                * IF ( 5 <= m, 1, 0 )
            + Table1[Jun]
                * IF ( 6 <= m, 1, 0 )
            + Table1[Jul]
                * IF ( 7 <= m, 1, 0 )
            + Table1[Aug]
                * IF ( 8 <= m, 1, 0 )
            + Table1[Sep]
                * IF ( 9 <= m, 1, 0 )
            + Table1[Oct]
                * IF ( 10 <= m, 1, 0 )
            + Table1[Nov]
                * IF ( 11 <= m, 1, 0 )
            + Table1[Dec]
                * IF ( 12 <= m, 1, 0 )
    )

     

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Hi amitchandak 

 

Is there a possibility in power bi without unpivot to get the ytd.

 

In my report if i unpivot the number of rows are doubled because of this.

 

Please suggest.

 

Regards,

Husna

v-chuncz-msft's avatar
v-chuncz-msft
Community Support
6 years ago

Anonymous 

 

You may try the measure below.

Measure =
VAR m =
    MAX ( 'Calendar'[Date].[MonthNo] )
RETURN
    SUMX (
        Table1,
        Table1[Jan]
            + Table1[Feb]
                * IF ( 2 <= m, 1, 0 )
            + Table1[Mar]
                * IF ( 3 <= m, 1, 0 )
            + Table1[Apr]
                * IF ( 4 <= m, 1, 0 )
            + Table1[May]
                * IF ( 5 <= m, 1, 0 )
            + Table1[Jun]
                * IF ( 6 <= m, 1, 0 )
            + Table1[Jul]
                * IF ( 7 <= m, 1, 0 )
            + Table1[Aug]
                * IF ( 8 <= m, 1, 0 )
            + Table1[Sep]
                * IF ( 9 <= m, 1, 0 )
            + Table1[Oct]
                * IF ( 10 <= m, 1, 0 )
            + Table1[Nov]
                * IF ( 11 <= m, 1, 0 )
            + Table1[Dec]
                * IF ( 12 <= m, 1, 0 )
    )