YTD calculation using Month name

[Anonymous]

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 ?

[amitchandak]

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]

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 · Accepted solution]

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