Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
@Anonymous
You can try the DAX formula below
Column =
VAR maxdate=max('TKD'[Year period])
VAR maxmonth=right(maxdate,2)
return if(datestable[period]<=value(maxmonth),"YTD","")
Proud to be a Super User!
@Anonymous
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())
Proud to be a Super User!
Since don't believe I can attach my .pbix file, I tried illustrating what I'm trying to do in excel....I hope this helps!
@Anonymous
You can try the DAX formula below
Column =
VAR maxdate=max('TKD'[Year period])
VAR maxmonth=right(maxdate,2)
return if(datestable[period]<=value(maxmonth),"YTD","")
Proud to be a Super User!
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)
@Anonymous , 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))
@Anonymous
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))
Proud to be a Super User!
I'm not sure what it was, but when I rebuilt the two graphs, it worked! Thanks so much for your help! @ryan_mayu
@Anonymous
Something like
YTD QTY forced=
var _max = eomonth(today(),0)
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))
@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.
User | Count |
---|---|
140 | |
113 | |
102 | |
73 | |
63 |
User | Count |
---|---|
135 | |
126 | |
110 | |
70 | |
61 |