Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to 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!
Hi @yuka_pbi You can try something like
Regards,
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
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
Hey, thanks for your replies.
But i got error like this
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!
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
Hi @yuka_pbi You can try something like
Regards,
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!
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
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
User | Count |
---|---|
89 | |
85 | |
66 | |
62 | |
57 |
User | Count |
---|---|
138 | |
112 | |
94 | |
82 | |
71 |