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

     

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