Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a dashboard with different KPI's. On top of the page is a Date year/month slicer.
On the same page I want a graph with the KPI value for the past 12 months. The 12 months should end with the selected Slicer year/month and count 12 months back.
When I leave the dependency between the slicer and the graph, then I only get 1 period. When I disconnect the dependency, I get all months in my dataset.
Here is the formula I set up:
DPO_M_o_M =
VAR Current_Date=max(dimDate[Date])
Var Max_Date=SELECTEDVALUE(dimDate[Date])
Var Bottom_Date=eomonth(Max_date,-18)
VAR AP=calculate(sum(DPO[MX_Act_AP]))
VAR COGS=calculate(sum(DPO[MX_Act_CostofRev3Mon]))
VAR AP_PM=calculate(sum(DPO[MX_Act_AP]),All(dimDate),DPO[MX_Date]=eomonth(Current_Date,-2)+1)
VAR COGS_PM=calculate(sum(DPO[MX_Act_CostofRev3Mon]),All(dimDate),DPO[MX_Date]=eomonth(Current_Date,-2)+1)
VAR DPO_Delta=divide(AP,COGS,0)*90-divide(AP_PM,COGS_PM,0)*90
VAR Result= If(Current_Date>=Bottom_Date && Current_Date<=Max_Date,DPO_Delta)
return
Result
I was expecting the graph only to be between bottom_date and Max_Date.
How can I make this work?
Solved! Go to Solution.
Hi @Robert1981 ,
I have created a simple sample, please refer to my pbix file to see if it helps you.
Create a date table.
Then create a measure.
Measure =
VAR _1 =
SELECTEDVALUE ( dimdate[Date] )
VAR _12 =
EDATE ( _1, -12 )
RETURN
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
ALL ( 'Table' ),
'Table'[date] >= _12
&& 'Table'[date] <= _1
&& 'Table'[month] = SELECTEDVALUE ( 'Table'[month] )
&& 'Table'[year] = SELECTEDVALUE ( 'Table'[year] )
)
)
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Robert1981 ,
I have created a simple sample, please refer to my pbix file to see if it helps you.
Create a date table.
Then create a measure.
Measure =
VAR _1 =
SELECTEDVALUE ( dimdate[Date] )
VAR _12 =
EDATE ( _1, -12 )
RETURN
CALCULATE (
SUM ( 'Table'[value] ),
FILTER (
ALL ( 'Table' ),
'Table'[date] >= _12
&& 'Table'[date] <= _1
&& 'Table'[month] = SELECTEDVALUE ( 'Table'[month] )
&& 'Table'[year] = SELECTEDVALUE ( 'Table'[year] )
)
)
How to Get Your Question Answered Quickly
If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .
Best Regards
Community Support Team _ Rongtie
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
hello @Robert1981
can you please share a presentation of the visually ou want to achieve with some dummy data.
best regards,