Forum Discussion

cesarvinas's avatar
cesarvinas
Advocate I
6 years ago
Solved

How to duplicate values from grouped measures into additional virtual groups

Hi. I have a table with places and corresponding population per year. Currently I only have data up to year 2018. Somethin like this:   Place Year Population a 2017 12 a 2018 11 b...
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    Now that you have your Years table, you could use an expression like this for your population to get the most recent non-blank population

     

    Population =
    VAR __thisyear =
        SELECTEDVALUE ( Years[Year] )
    VAR __summary =
        ADDCOLUMNS (
            FILTER ( ALL ( Years[Year] ), Years[Year] <= __thisyear ),
            "@Pop", CALCULATE ( SUM ( PlacePopulation[Population] ) )
        )
    VAR __Top1 =
        TOPN ( 1, FILTER ( __summary, [@Pop] > 0 ), Years[Year] )
    VAR __result =
        MAXX ( __Top1, [@Pop] )
    RETURN
        __result

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat