cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rahulrackup
Microsoft
Microsoft

Calculating current & prior year YTD and QTD dynamically with separate slicer for month and year

Hi Everyone,

I'm very new to Power BI, appreciate it if you could help me find the solution. My scenario is I need to calculate the total sum of a measure for the current and previous year YTD and QTD, please see below snip but the problem is there are two separate slicers one for FISCAL MONTH and other for YEAR. So the requirement is when a user selects any month and year in the slicer the total sum would be the sum of YTD and QTD to which the month falls to. 

For example if a person select YEAR (2020) and MONTH( Oct), formual should calcualte for YTD( From July through Sep 2020), YTD PY( From July through Sep 2019), QTD ( Should be two months Sep & Oct of 2020, as my year starts from July) and QTD PY ( for two months Sep & Oct for 2019 ).

 

Below measures i have used to calculate.

YTD = CALCULATE(sum(Sheet3[Value]),filter(ALL('Calendar Table'),'Calendar Table'[Fiscal Year]=SELECTEDVALUE('Calendar Table'[Fiscal Year])&&'Calendar Table'[Month Period]<=SELECTEDVALUE('Calendar Table'[Month Period])))
 
YTD PY = CALCULATE(sum(Sheet3[Value]),filter(ALL('Calendar Table'),'Calendar Table'[Fiscal Year]=SELECTEDVALUE('Calendar Table'[Fiscal Year])-1&&'Calendar Table'[Month Period]<=SELECTEDVALUE('Calendar Table'[Month Period])))
 
QTD = CALCULATE(sum(Sheet3[Value]),PARALLELPERIOD('Calendar Table'[Fiscal Year Date],0,QUARTER))
 
QTD PY = CALCULATE(SUM(Sheet3[Value]),DATESQTD(ENDOFQUARTER(DATEADD('Calendar Table'[Fiscal Year Date],-1,YEAR))))
 
But for all the columns measures numbers are not picking correctly. 

 

rahulrackup_0-1634742189448.png

 
Please help to fix this up.
 
Thanks,
Rahul
 
 
1 ACCEPTED SOLUTION
v-yangliu-msft
Community Support
Community Support

Hi  @rahulrackup ,

I created some data:

vyangliumsft_0-1635318620496.png

Here are the steps you can follow:

1. Create calculated column.

Fiscal Year = YEAR('Table'[date])
Month Period = MONTH('Table'[date])

2. Create calculated table.

Slice = SUMMARIZE('Table','Table'[date],'Table'[Fiscal Year],'Table'[Month Period])

3. Create measure.

YTD =
var _selecteddate=MIN('Slice'[date])
var _endate=EOMONTH(_selecteddate,-1)
var _startdate=EOMONTH(_selecteddate,-4)
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[date]>_startdate&&'Table'[date]<=_endate&&'Table'[Country/Region]=MAX('Table'[Country/Region])))
YTD_PY =
var _selecteddate=MIN('Slice'[date])
var _endate=EOMONTH(_selecteddate,-13)
var _startdate=EOMONTH(_selecteddate,-16)
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[date]>_startdate&&'Table'[date]<=_endate&&'Table'[Country/Region]=MAX('Table'[Country/Region])))
QTD =
var _selecteddate=MIN('Slice'[date])
var _endate=EOMONTH(_selecteddate,0)
var _startdate=EOMONTH(_selecteddate,-2)
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[date]>_startdate&&'Table'[date]<=_endate&&'Table'[Country/Region]=MAX('Table'[Country/Region])))
QTD_PY =
var _selecteddate=MIN('Slice'[date])
var _endate=EOMONTH(_selecteddate,-12)
var _startdate=EOMONTH(_selecteddate,-14)
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[date]>_startdate&&'Table'[date]<=_endate&&'Table'[Country/Region]=MAX('Table'[Country/Region])))

4. Use [Fiscal Year] and [Month Period] of the Slice table as slicers.

5. Result:

vyangliumsft_1-1635318620500.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

3 REPLIES 3
v-yangliu-msft
Community Support
Community Support

Hi  @rahulrackup ,

I created some data:

vyangliumsft_0-1635318620496.png

Here are the steps you can follow:

1. Create calculated column.

Fiscal Year = YEAR('Table'[date])
Month Period = MONTH('Table'[date])

2. Create calculated table.

Slice = SUMMARIZE('Table','Table'[date],'Table'[Fiscal Year],'Table'[Month Period])

3. Create measure.

YTD =
var _selecteddate=MIN('Slice'[date])
var _endate=EOMONTH(_selecteddate,-1)
var _startdate=EOMONTH(_selecteddate,-4)
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[date]>_startdate&&'Table'[date]<=_endate&&'Table'[Country/Region]=MAX('Table'[Country/Region])))
YTD_PY =
var _selecteddate=MIN('Slice'[date])
var _endate=EOMONTH(_selecteddate,-13)
var _startdate=EOMONTH(_selecteddate,-16)
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[date]>_startdate&&'Table'[date]<=_endate&&'Table'[Country/Region]=MAX('Table'[Country/Region])))
QTD =
var _selecteddate=MIN('Slice'[date])
var _endate=EOMONTH(_selecteddate,0)
var _startdate=EOMONTH(_selecteddate,-2)
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[date]>_startdate&&'Table'[date]<=_endate&&'Table'[Country/Region]=MAX('Table'[Country/Region])))
QTD_PY =
var _selecteddate=MIN('Slice'[date])
var _endate=EOMONTH(_selecteddate,-12)
var _startdate=EOMONTH(_selecteddate,-14)
return
CALCULATE(SUM('Table'[Amount]),FILTER(ALL('Table'),'Table'[date]>_startdate&&'Table'[date]<=_endate&&'Table'[Country/Region]=MAX('Table'[Country/Region])))

4. Use [Fiscal Year] and [Month Period] of the Slice table as slicers.

5. Result:

vyangliumsft_1-1635318620500.png

 

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

View solution in original post

amitchandak
Super User
Super User

@rahulrackup ,

 

 

YTD Corrected =

var _1 = maxx(allselected('Date'), 'Date'[Date])
var _max = format(eomonth(_1,-1),"YYYYMM")
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),format('Date'[Date],"YYYYMM")<=_max)

 

or


YTD Corrected =
var _1 = maxx(allselected('Date'), 'Date'[Date])
var _max = format(eomonth(_1,-1),"YYYYMM")
return
if(format(max('Date'[Date]),"YYYYMM")<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])),blank())

 

 

same way QTD

 

 

QTD Corrected =

var _1 = maxx(allselected('Date'), 'Date'[Date])
var _max = format(eomonth(_1,-1),"YYYYMM")
return
calculate(Sum('order'[Qty]),DATESQTD('Date'[Date]),format('Date'[Date],"YYYYMM")<=_max)

 

or

 

QTD Corrected =
var _1 = maxx(allselected('Date'), 'Date'[Date])
var _max = format(eomonth(_1,-1),"YYYYMM")
return
if(format(max('Date'[Date]),"YYYYMM")<=_max, calculate(Sum('order'[Qty]),DATESQTD('Date'[Date])),blank())

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin
Want To Learn Power BI
Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Sir, it's not working 

Helpful resources

Announcements
Power BI December 2021 Update_carousel 768x460.jpg

Check it Out!

Click here to read more about the December 2021 Updates!

User Group Leader Meeting January 768x460.png

Calling all User Group Leaders!

Don't miss the User Group Leader meetings on January, 24th & 25th, 2022.

Jan 2022 Dev Camp 768x460 copy.png

Power BI Dev Camp- January 27th, 2022

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors