Forum Discussion

Regular Visitor

2 years ago

Solved

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

Anonymous
2 years ago
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!
ytd demo.pbix30 KB
Anonymous
2 years ago
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!

Icon for Helper II rank

Helper II

2 years ago

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
Regular Visitor
2 years ago
Hey, thanks for your replies.

But i got error like this
- Anonymous
  Not applicable
  2 years ago
  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!
  ytd demo.pbix30 KB
  - yuka_pbi
    Regular Visitor
    2 years ago
    
    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

Related content