Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

"Fill in" values between dates

Hi,

 

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

BillIDLegislation StatusYear
166Proposed2009
166Tabled2009
166Passed2011
166Implemented2012
193Proposed2011
193Tabled2011
193Passed2011
193Implemented2014

 

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

BillIDLegislation StatusYear
166Proposed2009
166Tabled2009
166Tabled2010
166Passed2011
166Implemented2012
193Proposed2011
193Tabled2011
193Passed2011
193Passed2012
193Passed2013
193Implemented2014

 

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!

Need Help
  • DataZoe's avatar
    DataZoe
    6 years ago

    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's avatar
    v-deddai1-msft
    6 years ago

    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's avatar
    Anonymous
    6 years ago

    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.

5 Replies

  • DataZoe's avatar
    DataZoe
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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's avatar
      Anonymous
      Not applicable
      6 years ago

      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:

       

      • DataZoe's avatar
        DataZoe
        Icon for Microsoft Employee rankMicrosoft Employee
        6 years ago

        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.