"Fill in" values between dates

[Anonymous]

Hi,

I am currently trying to create a visual but struggling with some time intelligence calculations. My data has the following structure:

BillID	Legislation Status	Year
166	Proposed	2009
166	Tabled	2009
166	Passed	2011
166	Implemented	2012
193	Proposed	2011
193	Tabled	2011
193	Passed	2011
193	Implemented	2014

However, in order to make a visual I need to "fill in" the values where there are missing years for each BillID:

BillID	Legislation Status	Year
166	Proposed	2009
166	Tabled	2009
166	Tabled	2010
166	Passed	2011
166	Implemented	2012
193	Proposed	2011
193	Tabled	2011
193	Passed	2011
193	Passed	2012
193	Passed	2013
193	Implemented	2014

Does anyone know some smart DAX that could solve this problem? For reference, this is (roughly) the visual I am trying to create:

Any help appreciated, thanks!

[DataZoe · Accepted solution]

@Anonymous Ok, I have another solution for you:

Status Measure = COALESCE(min(Gap[Status]),

CALCULATE(min(Gap[Status]),PREVIOUSYEAR('Calendar'[Date])),

CALCULATE(min(Gap[Status]),PREVIOUSYEAR(PREVIOUSYEAR('Calendar'[Date]))),

CALCULATE(min(Gap[Status]),PREVIOUSYEAR(PREVIOUSYEAR(PREVIOUSYEAR('Calendar'[Date])))),

CALCULATE(min(Gap[Status]),PREVIOUSYEAR(PREVIOUSYEAR(PREVIOUSYEAR(PREVIOUSYEAR('Calendar'[Date]))))),

CALCULATE(min(Gap[Status]),PREVIOUSYEAR(PREVIOUSYEAR(PREVIOUSYEAR(PREVIOUSYEAR(PREVIOUSYEAR('Calendar'[Date]))))))

)

This one will look at this year, and then up to 5 years back for the status to bring forward. 

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

DataZoe has provided  a clear method but you need a calendar table. Or you can create a year table as mentioned by him above. Then use the following measure based on your sample data:

Measure = var a = MAX('All Years'[Value]) return COALESCE(MAX('Table'[Legislation Status]),CALCULATE(MAX('Table'[Legislation Status]),ALLEXCEPT('Table','Table'[BillID]),'Table'[Year] = a-1),CALCULATE(MAX('Table'[Legislation Status]),ALLEXCEPT('Table','Table'[BillID]),'Table'[Year] = a-2))

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZMKiHLutDZJo0JaoX7hR7QBON50OxZ57PjBM6gbiiqw8A?e=no0CV8

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[DataZoe]

@Anonymous You could create a calculated table with all the years you need, then create a relationship to the table. 

Modeling --> New Table

All Years = GENERATESERIES(minx('Calendar','Calendar'[Year]),maxx('Calendar','Calendar'[Year]),1)

Once you have that, change out the year on the visual to the new table's year. Then right-click on the year in the field list, and choose "Show items with no data"

[Anonymous]

Hi @DataZoe,

Thanks for the response. Unfortunatley that hasn't solved the issue as I am looking to actually "fill in" values for the cells, where if the value for the next year is blank, I fill in the value for the previous year.

Sorry for the crude picture editing but it would be from this:

to this:

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

DataZoe has provided  a clear method but you need a calendar table. Or you can create a year table as mentioned by him above. Then use the following measure based on your sample data:

Measure = var a = MAX('All Years'[Value]) return COALESCE(MAX('Table'[Legislation Status]),CALCULATE(MAX('Table'[Legislation Status]),ALLEXCEPT('Table','Table'[BillID]),'Table'[Year] = a-1),CALCULATE(MAX('Table'[Legislation Status]),ALLEXCEPT('Table','Table'[BillID]),'Table'[Year] = a-2))

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZMKiHLutDZJo0JaoX7hR7QBON50OxZ57PjBM6gbiiqw8A?e=no0CV8

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous · Accepted solution]

Hi Everyone,

Apologies for not replying sooner but I decided to instead create a Year & BillID scaffold the data warehouse before importing into Power BI.

This means I dont need to create DAX with a fixed number of dates and can instead work dynamically.

Thanks for the suggestions.

[DataZoe · Accepted solution]

@Anonymous Ok, I have another solution for you:

Status Measure = COALESCE(min(Gap[Status]),

CALCULATE(min(Gap[Status]),PREVIOUSYEAR('Calendar'[Date])),

CALCULATE(min(Gap[Status]),PREVIOUSYEAR(PREVIOUSYEAR('Calendar'[Date]))),

CALCULATE(min(Gap[Status]),PREVIOUSYEAR(PREVIOUSYEAR(PREVIOUSYEAR('Calendar'[Date])))),

CALCULATE(min(Gap[Status]),PREVIOUSYEAR(PREVIOUSYEAR(PREVIOUSYEAR(PREVIOUSYEAR('Calendar'[Date]))))),

CALCULATE(min(Gap[Status]),PREVIOUSYEAR(PREVIOUSYEAR(PREVIOUSYEAR(PREVIOUSYEAR(PREVIOUSYEAR('Calendar'[Date]))))))

)

This one will look at this year, and then up to 5 years back for the status to bring forward.