Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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
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
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())
Sir, it's not working
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |