Return dates from table PTD based on selected date value in filter

[MWare]

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

[MWare]

@lbendlin 

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 · Accepted solution]

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)

[lbendlin]

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.

[MWare]

Hi 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-zhangti]

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]

@v-zhangti 

Hi,

Thanks for your help, your solution does technically solve the issue however I wanted to be able to use this solution in a table and return the 'Sales' value for each day and also return the 'Sale Period to date' as of each date in the table.

If I add the 'Sales' measure and the 'Sales Period to date' measure to my table I get the latest Sales value for the date selected in the filter as well as the Sales Period to date value replicated for each of the dates instead of returning the Sales value and the Sales Period to date for each days in the table (if that makes sense).

See updated file attached

https://drive.google.com/file/d/1bE3BFQa-hhIUoCqedD_4v8hFDu8mQKV1/view?usp=sharing

[lbendlin]

The problem is that measures cannot return table values. They can only return scalar values.

Use the Date table to feed the slicer, not the Date_Period_Table table . Then you can use FILTERS or SELECTEDVALUE on the date slicer, compute the measure for each date in the regular calendar, and then use the measure as a visual level filter.

See attached