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 ,but since i have only month name im not sure how do i get that ?

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

     

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    My advice would be to unpivot and create a date like this

     

    Date = "1-" & Table[Month] & "-2020" //If you do not have year

    Date = "1-" & Table[Month] & "-" &  @Table[Year] //If you do have year

     

    Then you can use time intelligence

    YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

     

    To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
    https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
    https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
    https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

    • 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 )
    )