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

Hey, thanks for your replies.

But i got error like this

 

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

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

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