Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Help adjusting YTD formula to use max month of current year

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:

 

YTD Flag = IF(Dates[MonthOfYear]<MONTH(today()),"YTD",BLANK())
 
I need to replace the red portion with something to pull max month of the current year.
 
I'd really appreciate your help in making this adjustment!
 
Jordan

 

1 ACCEPTED 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","")

1.PNG

 

 

 

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

10 REPLIES 10
ryan_mayu
Super User
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())

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

@ryan_mayu @amitchandak 

 

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!

 

Capture.PNG

@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","")

1.PNG

 

 

 

 

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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.

 

  Capture.PNG

 

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)

 

Capture1.PNG@ryan_mayu @amitchandak 

 

 

 

@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))

 

 





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Anonymous
Not applicable

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
Not applicable

@ryan_mayu  Thanks.  I created a sample file.  How do I attach it here?

amitchandak
Super User
Super User

@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))

Anonymous
Not applicable

@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.

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.