cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jmays86
Helper IV
Helper IV

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

@jmays86 

 

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 II
Super User II

@jmays86 

 

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!




@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

@jmays86 

 

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

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 

 

 

 

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

 

 



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@jmays86 

 

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!




I'm not sure what it was, but when I rebuilt the two graphs, it worked!  Thanks so much for your help! @ryan_mayu 

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

amitchandak
Super User IV
Super User IV

@jmays86 

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



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

@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
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

July 2021 Update 768x460.png

Check it out!

Click here to read more about the July 2021 Updates

Top Solution Authors
Top Kudoed Authors