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.
I have a DAX that was suppose to return values based on date period slicer but returns blank.
The scenario is I have a date table and a dateDup table which are not related. They both contain all dates and MonthYear fields and the values are Apr-2023, May-2023 and so on. Another fields has PeriodmonthYear and the values are P1 Apr-23, P2 May-23 and so on. And aslo a financial Year e.g. FY 2022/23, FY 2023/24 etc.
I want to create a line chart that when Financial slicer is selected, let say FY 2023/24 and PeriodMonthYear slicer is selected, let say P1 Apr-23 then the line chart should return the last 12 months sum of Actual Target. i.e values from P12 Mar-22 to P1 Apr-23. And if i select P2 May-23, it should return line chart with P1 Apr-22 to P2 May-23
hence, the Dax i used is
Actual S4 | Last 12 Months =
var _sum = SUM('KT Targets Scenarios'[Actual S4 Stretch met])
var maxdate = MAX(DateTable[Date])
var maxdate_12MonthsAgo = EOMONTH(maxdate, -12)
var Result =
IF(
HASONEVALUE( DateTableDup[Month Year] ) &&
MAX( DateTableDup[Date]) <= maxdate &&
MIN( DateTableDup[Date]) > maxdate_12MonthsAgo ,
CALCULATE(_sum,
FILTER( ALL(DateTable[Period_MonthYear]),
DateTable[Period_MonthYear] = Values( DateTableDup[Period_MonthYear])
)
),
BLANK()
)
return
Result
This is returning blank.
Note my datetables are not related.
When I add Company slicer and select All, then is wasn't blank but show all values from the 12 months but same values see below screenshot.
This is wrong. please how can i get dax working well.
I need your help.
Solved! Go to Solution.
@tek01 , when you select one value, and want to display more than that, you need a slicer on the independent table(Date table here)
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
@tek01 , when you select one value, and want to display more than that, you need a slicer on the independent table(Date table here)
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
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 |
---|---|
113 | |
99 | |
82 | |
70 | |
60 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |