Forum Discussion
Helper IReturn dates from table PTD based on selected date value in filter
Hi all,
Looking for a bit of help with a measure please.
I have created a measure to calculate the 'period to date' sum of a column. This works well and with this measure on a KPI card users can select a date via the date slicer and the KPI card will display the period to date value up to and including the selected date.
I also have plotted the measure on a graph however I only want to see the dates on the X axis that are included within the period to date results. Needless to say I am having difficulties with this, can anyone offer any help or ideas please?
Graph date 2 =
VAR LastDayAvailable = MAX ('Dates_Pd_Wk_Yr'[Date slicer])
VAR LastPeriodAvailable = MAX ('Dates_Pd_Wk_Yr'[Year_Period])
VAR Result =
CALCULATE(
VALUES('Dates_Pd_Wk_Yr'[Date]),
ALLEXCEPT('Dates_Pd_Wk_Yr',Dates_Pd_Wk_Yr[Date slicer]),
'Dates_Pd_Wk_Yr'[Date slicer] <=LastDayAvailable,
'Dates_Pd_Wk_Yr'[Year_Period] = LastPeriodAvailable
)
RETURN
Result
I've attached the .pbix file to hopefully make things a bit easier to understand
https://drive.google.com/file/d/1KAmz2exA0D2I9WyAibHiC-g2etMi4tWc/view?usp=sharing
Thanks
Marc
oh, sorry, didn't internalize that part of the requirement.
Measure = Var p=CALCULATE(max(Date_Period_Table[Period]),ALL(Date_Period_Table),Date_Period_Table[Date]=SELECTEDVALUE('Date'[Date])) return if(SELECTEDVALUE(Date_Period_Table[Period])=p && SELECTEDVALUE(Date_Period_Table[Date])<=SELECTEDVALUE('Date'[Date]),1,0)
7 Replies
- lbendlin
Super User
Thank you for providing the sample data. Your PBIX is way too complex (and probably rather slow). Please provide a sample pbix that only focuses on the issue you are trying to solve, without any of the unrelated tables and visuals. Please indicate the expected outcome.
MWareHi apologies for the delay in getting back to you and making matters even more complicated than they needed to be. I've recreated the data and measure to and added a description of what I'm trying to achieve in the pbix file, is this any better? Thanks in advance
https://drive.google.com/file/d/19W2-X-TXfYrp5Fg7nyVHQy02NEQNhZJI/view?usp=sharing
v-zhangtiCommunity Support
Hi, MWare
You can try the following methods. First create a new date table.
Table:
Date = CALENDAR(MIN(Date_Period_Table[Date]),MAX(Date_Period_Table[Date]))Measure:
Measure = VAR _Mindate = CALCULATE ( MIN ( Date_Period_Table[Date] ), FILTER ( ALL ( Date_Period_Table ), [Period] = SELECTEDVALUE ( Date_Period_Table[Period] ) ) ) VAR _Maxdate = SELECTEDVALUE ( Date_Period_Table[Date] ) RETURN IF ( SELECTEDVALUE ( 'Date'[Date] ) >= _Mindate && SELECTEDVALUE ( 'Date'[Date] ) <= _Maxdate, 1, 0 )Put Measure into the Fliter of the date table and set it equal to 1.
Is this the result you expect?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- MWare
Thank you that makes sense, I have made the adjustments but it's still not quite correct as it's returning data that's equal to the date slicer and beyond, up to the end of the period. I need it to return data from the start of the period up to the date in the date slicer. I think the measure needs to be tweaked but when I read it, it makes sense ....?
Updated file in the link below
https://drive.google.com/file/d/1Fko9R9w325vp4_QdDNpSE8_5SOHCTa_K/view?usp=sharing
- lbendlin
oh, sorry, didn't internalize that part of the requirement.
Measure = Var p=CALCULATE(max(Date_Period_Table[Period]),ALL(Date_Period_Table),Date_Period_Table[Date]=SELECTEDVALUE('Date'[Date])) return if(SELECTEDVALUE(Date_Period_Table[Period])=p && SELECTEDVALUE(Date_Period_Table[Date])<=SELECTEDVALUE('Date'[Date]),1,0)