How to calculate using the same measure when related date has no data (past and future)?

[Anonymous]

Basically what I trying to achieve is to diplay the min measure's value to past dates which have no data and display the max measure's value to future dates which have no data.

Here you can find a Sample.pbix with a minimum data set.

In the following screenshot I annotated what I'd like to get as the result...



I hope someone can shed some light into this.

Thanks in advance for your help.

[Anonymous]

Hi @Anonymous 

Here's what I've come up with. 

.pbix

I used what sam mckay calls a virtual table to build out the max and min, and then call those values depending on whether the date is in the past or future based on todays date.

[Anonymous]

Hey @Anonymous,

I downloaded the .pbix you linked to but it seems to be the one I originally uploaded as a Sample. 😐

[Anonymous]

Oops!

Here, try this formula in place of your current Plan Count:

Plan Count = 
VAR week_date = SELECTEDVALUE( 'Planning Weeks'[WeekDate])
VAR plan_count = SUMMARIZE( 'Export', 'Export'[Week_Imported], 
                    "Plan Count", CALCULATE (COUNT('Export'[Plan]),
                        'Export'[Plan]="YES" || 'Export'[Plan]="_"))
RETURN
IF (
   week_date < TODAY(),
   MINX( plan_count, [Plan Count]),
    MAXX(plan_count, [Plan Count]))

[Anonymous]

@Anonymous, why did you use TODAY()? The measure should be dynamic, that is, the data in Export can have dates added or removed.

The logic is this:

- all dates in the past comparing to the FIRST date available in Export table for the date (30-Dec-19), should get the value 1064;
- all dates in the future comparing to the LAST date available in Export table for the date (06-Jan-20), should get the value 1073;
- all dates from Export should get their own plan count values.

Something like this:

WeekDate    Plan Count
.
.
.
12/09/2019  1064 |
12/16/2019  1064 | - PAST dates
12/23/2019  1064 |
12/30/2019  1064 | <= all dates in the past should get this value
01/06/2020  1073 | <= all dates in the future should get this value
01/13/2020  1073 |
01/20/2020  1073 | - FUTURE dates
01/27/2020  1073 |
.
.
.

The tricky part is how to do the logic to get MIN(Export[Week_Imported]) and MAX(Export[Week_Imported]) inside the measure and fill the values correctly.

[Anonymous]

You want the dates in your export table to decide the before and after? Are you ever going to have more than 2 dates in that table?

Plan Count = 
VAR week_date = SELECTEDVALUE( 'Planning Weeks'[WeekDate])
VAR plan_count = SUMMARIZE( 'Export', 'Export'[Week_Imported], 
                    "Plan Count", CALCULATE (COUNT('Export'[Plan]),
                        'Export'[Plan]="YES" || 'Export'[Plan]="_"))
RETURN
IF (
   week_date <= MINX('Export', 'Export'[Week_Imported]),
   MINX( plan_count, [Plan Count]),
IF ( week_date >= MAXX('Export', 'Export'[Week_Imported]),
    MAXX(plan_count, [Plan Count])))

This formula will create a table like the one you've given as an example. 

[Anonymous]

@Anonymous

You want the dates in your export table to decide the before and after?
Yes... the FIRST and LAST dates in Export table will control the before and after.

Are you ever going to have more than 2 dates in that table?
Yes, the Export table can have data for more than 2 weeks... these 2 weeks of data in the sample is just to have a minimum set of data to play with.

#######

I opened Sample.xlsx file in the Google folder I shared in the question and added a new set of rows with date 1/13/2020. Refreshed the .pbix model. Now .the last formula you shared doesn't work anymore.