Create a DAX calendar table and bring data in

[Anonymous]

I have a table that looks like this:

Date	Country	Result
01-Jan	Japan	1
01-Jan	Germany	2
01-Jan	USA	1.4
03-Jan	Germany	5
03-Jan	USA	2.3
07-Jan	USA	3
08-Jan	Japan	2
08-Jan	Germany	2
08-Jan	USA	7.1

I want to create a new table whose first column is a calendar. The second and third columns would show the data of the original table. It would look like this:

Date	Country	Result
01-Jan	Japan	1
01-Jan	Germany	2
01-Jan	USA	1.4
02-Jan	Japan	0
02-Jan	Germany	0
02-Jan	USA	0
03-Jan	Japan	0
03-Jan	Germany	5
03-Jan	USA	2.3
04-Jan	Japan	0
04-Jan	Germany	0
04-Jan	USA	0
05-Jan	Japan	0
05-Jan	Germany	0
05-Jan	USA	0
06-Jan	Japan	0
06-Jan	Germany	0
06-Jan	USA	0
07-Jan	Japan	0
07-Jan	Germany	0
07-Jan	USA	3
08-Jan	Japan	2
08-Jan	Germany	2
08-Jan	USA	7.1

Does anyone know how to do this?

I created a calendar table with the following code

NewTable = CALENDAR ( min('Table'[Date]), max('Table'[Date]) )

I added the result with a column

ResultColumn = 
CALCULATE (
    SUM ( 'Table'[Result] ),
    FILTER ('Table', 'Table'[Date] = NewTable[Date] )
)

But how do I add the column with the countries in there?

[Zubair_Muhammad]

Hi @Anonymous

You can create a CALCULATED TABLE transforming your Table into Desired Format.

Basically it Adds the missing dates and cross joins the dates with all the Countries that existed before that date

Assuming your TableName is Table1.

Go to Modelling Tab>>New Table

Calculated Table =
VAR AllDates =
    CALENDAR ( MIN ( Table1[Date] ), MAX ( Table1[Date] ) )
VAR MissingDates =
    EXCEPT ( AllDates, VALUES ( Table1[Date] ) )
VAR temp =
    UNION ( ADDCOLUMNS ( MissingDates, "Country", BLANK (), "Result", 0 ), Table1 )
VAR temp1 =
    GENERATE (
        SUMMARIZE ( temp, [Date] ),
        CALCULATETABLE (
            FILTER ( SUMMARIZE ( temp, [Country] ), [Country] <> BLANK () ),
            FILTER ( temp, [Date] <= EARLIER ( [Date] ) )
        )
    )
RETURN
    ADDCOLUMNS (
        temp1,
        "Result",
        VAR result =
            LOOKUPVALUE ( Table1[Result], Table1[Country], [Country], Table1[Date], [Date] )
        RETURN
            IF ( ISBLANK ( result ), 0, result )
    )

[Zubair_Muhammad · Accepted solution]

@Anonymous

Please see the attached file as well

[Anonymous]

Thank you very much  🙂