topic Measure to get plan for every day in DAX Commands and Tips

Measure to get plan for every day

Anonymous — Sun, 15 Jan 2023 21:32:59 GMT

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.

Re: Measure to get plan for every day

Anonymous — Sun, 15 Jan 2023 22:24:33 GMT

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))}

Re: Measure to get plan for every day

Anonymous — Mon, 16 Jan 2023 06:36:44 GMT

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?

Re: Measure to get plan for every day

FreemanZ — Mon, 16 Jan 2023 08:01:03 GMT

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: