Hello, I'm currently using a calculated column that uses current month to flag YTD months, but I would like to change it to use max month of the current year in my "TKD" table to do the same thing. The reason is my data doesn't refresh immediately upon each new month, which causes a mismatch between current and prior years when I'm comparing YTD metrics.
Below is my current formula:
Solved! Go to Solution.
I am not sure if below DAX formula is what you want. It will be better if you share the sample file.
YTD Flag = VAR maxdate= month(max(TDK[current year])) Return IF(Dates[MonthOfYear]<maxdate,"YTD",BLANK())
Thanks! That made my year to date calcs perfect. Now I need a way to clear the filter to provide the option to view full year data, but I can't seem to clear the filter (slicer) for the top two graphs. It works for the bottom two, however. Is there something I need to change with the data types or interactions? The interactions seem to be the same for all for graphs.
No selection on this slicer. Ideally, I'd like to toggle back between YTD and full year but happy with just being able to clear YTD if easier! (Again, somehow it's working for the bottom two visuals, which are measures)
@jmays86 , for full year you can use end of year
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Previous Year = CALCULATE(SUM('order'[Qty]), PREVIOUSYEAR('Date'[Date]))
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
I am not sure how you write the measures and how to create visuals.
I guess your measure make the results to be fixed.
Maybe you can try to modify the measure like below.
if selectedvalue = "YTD", YTDmeasure, if(selectedvalue = "Full year", fullyearmeasure))
YTD QTY forced=
var _max = eomonth(today(),0)
@amitchandakI only need to adjust the red part of my formula to pull the max month from the current year from my "YTD" table. This is going to affect 50 different metrics across my report, and I do not want to create a new measure for each one.
Check out new user group experience and if you are a leader please create your group!
Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.
Click here to read more about the July 2021 Updates