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 cu...
  • 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 )
    )
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 )
    )