CODEWITHSUNDEEP
powerbidax
SUMguy
SUMguy

Reputation: 1673

DAX - evenly distribute data across dates

In Power BI, I have a standard Date dimension tied to a fact table.

I have created a line chart with the Dates on the x axis, and the following measure on the y axis.

CALCULATE(SUM(Table[Forecast]), DATESMTD('Date'[Date])

The line chart is filtered to the current month.

In my database, I only have data for the FIRST of the month. So for example, for the current month, my value looks something like this in the database:

Table

 Date           Forecast
 12-01-2017     23000

This means that the line on my line graph generated by the above calculation is a flat line - as one would expect.

What I am trying to do is create a calculation that would take the cumulative value generated by the above calculation and distribute that among the visible dates. So because the graph is filtered to the current month, the first of the month should have a value of 23,000 / 31 days = 741.93

The 2nd of the month should have 741.93 * 2 = 1483.87

The 3rd of the month should have 741.93 * 3 - 2225.8 ...and so on

Upvotes: 2

Views: 3467

Answers (1)

Alexis Olson
Alexis Olson

Reputation: 40224

Create a new table that has all of the days from all of the months of your forecast table.

Table2 = CALENDAR(MIN(Table[Date]),EOMONTH(MAX(Table[Date]),0))

Now on this table, you can define a custom column which has the values you want.

DayForecast = LOOKUPVALUE(Table[Forecast],Table[Date],EOMONTH(Table2[Date],-1)+1) *
              DIVIDE(DAY(Table2[Date]),DAY(EOMONTH(Table2[Date],0)))

What this does is look up the forecast for the current month and then multiplies it by the ratio of the current day of the month over the total number of days in the month.

Upvotes: 2

Related Questions