Forum Discussion

jenmm's avatar
jenmm
Regular Visitor
8 years ago
Solved

Copy Max Date data to other months

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_PeriodGroupCount
201801Group112
201801Group29
201801Group312
201802Group121
201802Group223
201802Group325
201803Group133
201803Group234
201803Group332
201804Group133
201804Group234
201804Group332
201805Group133
201805Group234
201805Group332
201806Group133
201806Group234
201806Group332
201807Group133
201807Group234
201807Group332
201808Group133
201808Group234
201808Group332
201809Group133
201809Group234
201809Group332
201810Group133
201810Group234
201810Group332
201811Group133
201811Group234
201811Group332
201812Group133
201812Group234
201812Group332

*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's avatar
    Zubair_Muhammad
    8 years ago

    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 )
    )

3 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

    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 )
    )