Measure to get plan for every day

[Anonymous]

My goal is create a measure that gets a plan value for every dayfrom my calendar.

I have 2 tables - calendar and plans, then I create relationship on columns Date -> Plan from date. But when I try to create a "Visual I want" showing on my screenshot, I get what I get (4th table).

I tried to use option Show items with no data and create my own measure but I was failed to get a correct result. Your help is very appreciated.

[Anonymous]

Hello @Anonymous this is easily solved in power query

Go to transform data, select your table

Add a new column with the below formula

{Number.From([plan from date])..Number.From([plan to date])}

Then on the new column expand to new rows and change type to date

If you dont have a "until date" column then just add an index column then add a new column

Type in : Try [plan from date] {[Index] + 1} otherwise null

Then just create a new column again 

{Number.From([plan from date])..Number.From(Date.AddDays([new column],-1))}

[Anonymous]

HI eliasayy,

I have date 9999-12-31 for active plans and a lot of products that have it plan target (I just did not mention it in my example for simplicity) and some plans started from 2017 year.

I have around 100к rows in my PlansTable now and if I create additional rows for every day it will crete me a huge table.

Is there any chans to do that with a measure only?

[FreemanZ · Accepted solution]

hi @Anonymous 

You may write a measure like this:

measure = 
VAR _date = SELECTEDVALUE(CalendarTable[Date]) 
RETURN
MINX(
    FILTER(
        ALL(PlansTable),
        PlansTable[Plan from date]<=_date
            &&PlansTable[Plan to date]>=_date
    ),
    PlansTable[Value]
)

it worked like this: