Forum Discussion

yuka_pbi's avatar
yuka_pbi
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    
Need Help
  • Anonymous's avatar
    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!

  • Anonymous's avatar
    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!

     

     

yuka_pbi's avatar
yuka_pbi
Regular Visitor
2 years ago

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 

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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