Forum Discussion
Make YTD Value
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
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!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!
7 Replies
For Time Intelligence functions you should have date field
to create date field with your month name column follow below daxDateColumn = 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 blogHey, thanks for your replies.
But i got error like thisHi 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!
