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.
Need help in a meausre to calculate last year ratio when user slelect period from slicer.
Current Ratio LY =
Solved! Go to Solution.
I have found a solution let me explain it to you,
When we want to bypass a slicer we will use all filter.
,In such cases better to have a separate date/year period table with numeric year and period
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),'Period'[Year]=max('Period'[Year]) && Period[Period]=max(Period[Period])))
Last year same Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),'Period'[Year]=max('Period'[Year])-1 && Period[Period]=max(Period[Period])))
But if you need this period vs last period
you need have period rank on YYYYPP
New column on Date/period Table
Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)
Measures
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))
pls check the scnerio, only slection of period come up with blank, even its calculating year correct , but when i subtruct 1 from year of period it contains blank in measure.
Let me clear scenrio, I am having GL balance , Period year as 2022,period_name as Jun-21-22 and I have put Period_name in slicer, when user select period I calculate period year from it as year = calculate (max(period_year),period_name=selected_period) it return me the year then what I do, I subtruct 1 from calculated year....
After that I pass that year to measure for calcualtion as above.
the issue is when user select from slicer it narrow downs the data to that period_selection only.
now pls help to cater the data from previous year on selection.
@pwc_ananta , You need to have separate period /date table with sortable Year, Period, and Year Period(YYYYPP) and Year Period Rank
where Period Rank column is
Period Rank = RANKX(all(Period),Period[year period],,ASC,Dense)
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])))
Last Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),Period[Period Rank]=max(Period[Period Rank])-1))
This Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),'Period'[Year]=max('Period'[Year]) && Period[Period]=max(Period[Period])))
Last year same Period = CALCULATE(sum('Table'[Qty]), FILTER(ALL(Period),'Period'[Year]=max('Period'[Year])-1 && Period[Period]=max(Period[Period])))
New column if needed
Period Day =datediff([Period Start date] , [Date],Day) +1
PTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank]) && [Period Day] <=max([Period Day])))
LPTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Period Rank]=max('Date'[Period Rank])-1 && [Period Day] <=max([Period Day])))
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s
I have found a solution let me explain it to you,
When we want to bypass a slicer we will use all filter.
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 |
---|---|
106 | |
104 | |
79 | |
68 | |
61 |
User | Count |
---|---|
144 | |
104 | |
103 | |
82 | |
70 |