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

[rahulrackup]

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. 

Please help to fix this up.

Thanks,

Rahul

[v-yangliu-msft · Accepted solution]

Hi  @rahulrackup ,

I created some data:

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:

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

[amitchandak]

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

[rahulrackup]

Sir, it's not working