Forum Discussion
DAX/M Query
Hi Team,
Good Morning!
I need Sumif calculation for YDT process insert into summary table, see the below screenshot,
Summary Table- need to add "YTD-20" column for few months sum calculation.
Thanks,
KVB
Hi, Anonymous
Based on your description, I create data to reproduce your scenario.
Table:
You may create a measure as below.
Result = CALCULATE( SUM('Table'[Value]), FILTER( ALL('Table'), 'Table'[Date]<=MAX('Table'[Date])&& 'Table'[GHT] = SELECTEDVALUE('Table'[GHT]) ) )Then you may use a matrix visual to diplay the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 Replies
- amitchandakSuper User
Anonymous , not very clear, but you can get YTD wit date table
YTD QTY = TOTALYTD(Sum('order'[Qty]),'Date'[Date]) LYTD QTY = TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year)) Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date])) YTD QTY forced= var _max = today() return calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max) //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max)) LYTD QTY forced= var _max = date(year(today())-1,month(today()),day(today())) return CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max) //TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max) YTD QTY forced= var _max = maxx('order',[Order date]) return calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max) //calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max)) LYTD QTY forced= var _max1 =maxx('order',[Order date]) var _max = date(year(_max1)-1,month(_max1),day(_max1)) return CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max) //TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.- AnonymousNot applicable
- v-alq-msftCommunity Support
Hi, Anonymous
Based on your description, I create data to reproduce your scenario.
Table:
You may create a measure as below.
Result = CALCULATE( SUM('Table'[Value]), FILTER( ALL('Table'), 'Table'[Date]<=MAX('Table'[Date])&& 'Table'[GHT] = SELECTEDVALUE('Table'[GHT]) ) )Then you may use a matrix visual to diplay the result.
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.