Make YTD Value

[yuka_pbi]

Hey i have a transcantion data like this:

and master table month format like this:

Can i make value YTD when i dont have a date type column?

Thanks

[v-jingzhan-msft · Accepted solution]

Hi @yuka_pbi 

@PowerBigginer 's formula is DAX. You should use that in Power BI Desktop, not in Power Query Editor. Click "Close&Apply" and add a new column here

BTW, if you only have monthly data and the two tables are joined on Month_ID column, you can compute the YTD without adding the date column. Here is a measure sample:

YTD = CALCULATE(SUM(Revenue[Revenue]),ALLSELECTED(Revenue),Revenue[Month_ID]<=MAX('Date'[Month_ID]),'Date'[Year_ID]=MAX('Date'[Year_ID]))

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[PowerBigginer]

 For Time Intelligence functions you should have date field 
to create date field with your month name column follow below dax 

DateColumn = DATEVALUE("01-" & 'YourTable'[Month] & "-" & 'YourTable'[Year])

for more time inteligence functions check out https://powertipstricks.blogspot.com/ blog
to create calendar dim table in your model check out
https://powertipstricks.blogspot.com/2024/01/create-calendar-table-in-power-bi-using.html blog

[yuka_pbi]

Hey, thanks for your replies.

But i got error like this

[v-jingzhan-msft · Accepted solution]

Hi @yuka_pbi 

@PowerBigginer 's formula is DAX. You should use that in Power BI Desktop, not in Power Query Editor. Click "Close&Apply" and add a new column here

BTW, if you only have monthly data and the two tables are joined on Month_ID column, you can compute the YTD without adding the date column. Here is a measure sample:

YTD = CALCULATE(SUM(Revenue[Revenue]),ALLSELECTED(Revenue),Revenue[Month_ID]<=MAX('Date'[Month_ID]),'Date'[Year_ID]=MAX('Date'[Year_ID]))

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[yuka_pbi]

Thanks a lot for your solution, thats work!!.
Anyway, how about if i want to make Last Year YTD?? I try with this dax and not work

LY YTD = VAR CurrentMonth = SELECTEDVALUE(LU_MONTH[Month_ID]) VAR LastMonth = CurrentMonth - 12 RETURN CALCULATE(SUM(FACT_REVENUE_SUMMARY[Revenue_Net])/1000, ALLSELECTED(LU_MONTH[Month_ID]), LU_MONTH[Month_ID]<=MAX(LU_MONTH[Month_ID]), LU_MONTH[Year_ID]= MAX(LU_MONTH[Year_ID])) 

Thanks before

[v-jingzhan-msft · Accepted solution]

Hi @yuka_pbi  You can try something like 

 

LY YTD = CALCULATE(SUM(FACT_REVENUE_SUMMARY[Revenue_Net])/1000, ALLSELECTED(LU_MONTH[Month_ID]), LU_MONTH[Month_ID]<=MAX(LU_MONTH[Month_ID])-12, LU_MONTH[Year_ID]= MAX(LU_MONTH[Year_ID])-1)

Regards,
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[yuka_pbi]

Ahh, i see. actually i had a though, but idk to put the calculation (-12) where position lol.
thankyou so much, thats work on me 

[v-jingzhan-msft]

Yes you are almost there. Additionally here is a blog that introduces the running total pattern. It may be helpful for you: Computing running totals in DAX - SQLBI