Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
jenmm
Regular Visitor

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. 

2 ACCEPTED SOLUTIONS
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

View solution in original post

@jenmm

 

Please see attached file

 

copy.png


Regards
Zubair

Please try my custom visuals

View solution in original post

3 REPLIES 3
Zubair_Muhammad
Community Champion
Community Champion

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

Regards
Zubair

Please try my custom visuals

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

@jenmm

 

Please see attached file

 

copy.png


Regards
Zubair

Please try my custom visuals

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.