Copy Max Date data to other months

[jenmm]

Hi Everyone,

I have been building dashboards using Qlik and has had a chance to move to Power BI recently.

I'm currently creating a dashboard that will display 12 months worth of data for the current year.

Our current data set is only limited to 3 months worth (March). Given this, I wanted to copy the data from the max date (March) to the rest of the month (April to December).  In Qlik, what we usually do is just create an expression on the chart where in if the dates is greater than the MAX, we equate it to the MAX date data. 

*Expected Output*

Reporting_Period	Group	Count
201801	Group1	12
201801	Group2	9
201801	Group3	12
201802	Group1	21
201802	Group2	23
201802	Group3	25
201803	Group1	33
201803	Group2	34
201803	Group3	32
201804	Group1	33
201804	Group2	34
201804	Group3	32
201805	Group1	33
201805	Group2	34
201805	Group3	32
201806	Group1	33
201806	Group2	34
201806	Group3	32
201807	Group1	33
201807	Group2	34
201807	Group3	32
201808	Group1	33
201808	Group2	34
201808	Group3	32
201809	Group1	33
201809	Group2	34
201809	Group3	32
201810	Group1	33
201810	Group2	34
201810	Group3	32
201811	Group1	33
201811	Group2	34
201811	Group3	32
201812	Group1	33
201812	Group2	34
201812	Group3	32

*Only the 201801, 201802 and 201803 is the data that we have.

I was able to create a table consisting of different columns representing the different months (12 months) using IF statement and creating a variable for MAX date. However, since the date is broken down to different columns, I'm unable to use it for the other visuals (i.e. charts) as it is being read as a different value.

Samp_Actuals = 
var vMaxDate = MAX([Reporting_Period])

RETURN 

SUMMARIZE([Group_ID]
    ,"201801", IF(vMaxDate < 201801,CALCULATE(COUNT([Member_ID]),[Reporting_Period] = vMaxDate),CALCULATE(COUNT([Member_ID]),[Reporting_Period] = 201801))  
    ,"201802", IF(vMaxDate < 201802,CALCULATE(COUNT([Member_ID]),[Reporting_Period] = vMaxDate),CALCULATE(COUNT([Member_ID]), [Reporting_Period] = 201802))...


and so on.....

Any help is greatly appreciated!

NOTE: Table was an aggregation of a certain group ID from another table. Reporting Period is on an integer form since it was extracted from DB. 

[Zubair_Muhammad · Accepted solution]

Hi @jenmm

Try this calculated table

from the modelling tab>>New Table

Table =
VAR starting =
    MIN ( Table1[Reporting_Period] )
VAR MissingRows =
    CROSSJOIN ( GENERATESERIES ( Starting, Starting + 11 ), ALL ( Table1[Group] ) )
RETURN
    ADDCOLUMNS (
        MissingRows,
        "Count",
        VAR mycalc1 =
            CALCULATE (
                SUM ( Table1[Count] ),
                FILTER (
                    Table1,
                    Table1[Group] = EARLIER ( [Group] )
                        && Table1[Reporting_Period] = EARLIER ( [Value] )
                )
            )
        VAR mycalc2 =
            CALCULATE ( MAX ( Table1[Count] ), Table1[Group] = EARLIER ( Table1[Group] ) )
        RETURN
            IF ( ISBLANK ( mycalc1 ), mycalc2, mycalc1 )
    )

[Zubair_Muhammad · Accepted solution]

Hi @jenmm

Try this calculated table

from the modelling tab>>New Table

Table =
VAR starting =
    MIN ( Table1[Reporting_Period] )
VAR MissingRows =
    CROSSJOIN ( GENERATESERIES ( Starting, Starting + 11 ), ALL ( Table1[Group] ) )
RETURN
    ADDCOLUMNS (
        MissingRows,
        "Count",
        VAR mycalc1 =
            CALCULATE (
                SUM ( Table1[Count] ),
                FILTER (
                    Table1,
                    Table1[Group] = EARLIER ( [Group] )
                        && Table1[Reporting_Period] = EARLIER ( [Value] )
                )
            )
        VAR mycalc2 =
            CALCULATE ( MAX ( Table1[Count] ), Table1[Group] = EARLIER ( Table1[Group] ) )
        RETURN
            IF ( ISBLANK ( mycalc1 ), mycalc2, mycalc1 )
    )

[jenmm]

Woah! It worked! Thank you so much! Greatly appreciate the help, @Zubair_Muhammad! 

[Zubair_Muhammad · Accepted solution]

@jenmm

Please see attached file