"Add" a missing row

[Greg_Deckler]

OK, I have an interesting one here. I need a DAX solution for this.

First, I have a standard Calendar table define thus:

Calendar = CALENDAR(DATE(2017,1,1),DATE(2018,12,31))

In this Calendar table I have the following columns:

Year = YEAR([Date])
Month = MONTH([Date])
YearMonth = CONCATENATE('Calendar'[Year],FORMAT([Month],"0#"))

Then I have a Products table with this information:

Year Month Product Sum YearMonth

2018	1	CW	10	201801
2017	12	CW	20	201712
2017	11	CW	15	201711
2018	1	XD	20	201801
2018	12	XD	30	201812

What I need to end up wtih is this table so that I can compute a true value for the AVERAGE of "Sum":

Year Month Product Sum YearMonth

2018	1	CW	10	201801
2017	12	CW	20	201712
2017	11	CW	15	201711
2018	1	XD	20	201801
2017	12	XD	30	201712

  2018       11          XD         0            201711

The red is the row I need to magically "add". I can get a "starter" table like this:

Table = 
VAR StartDate = DATE(YEAR(TODAY()),MONTH(TODAY()),1)-1
VAR EndDate = StartDate - 90
VAR Month1 = MONTH(StartDate)
VAR Year1 = YEAR(StartDate)
VAR Month2 = IF(Month1=1,12,Month1-1)
VAR Year2 = IF(Month1=1,Year1-1,Year1)
VAR Month3 = IF(Month2=1,12,Month2-1)
VAR Year3 = IF(Month2=1,Year2-1,Year2)
VAR MyDataTable = FILTER(SUMMARIZE('Calendar','Calendar'[Year],'Calendar'[Month],'Calendar'[YearMonth]),('Calendar'[Year]=Year1 && 'Calendar'[Month]=Month1) || ('Calendar'[Year]=Year2 && 'Calendar'[Month]=Month2) || ('Calendar'[Year]=Year3 && 'Calendar'[Month]=Month3))
RETURN MyDataTable

This returns a table like this:

Year Month YearMonth

2017	11	201711
2017	12	201712
2018	1	201801

My thought here is that I could do some kind of DAX join with my Products table or something to end up with the table I wanted but for the life of me I cannot NATURALINNERJOIN or NATURALLEFTOUTERJOIN to work.

Any ideas? I truly do not have any information for product "XD" for November of 2017 but I really need it in order to compute the correct average.

[Vvelarde · Accepted solution]

@Greg_Deckler

Hi, lets try with this:

Product Table

Table =
VAR TEMPTABLE =
    DISTINCT (
        SELECTCOLUMNS (
            Products;
            "YEARWT"; Products[Year];
            "MONTHWT"; Products[Month];
            "YEARMONTHWT"; Products[YearMonth]
        )
    )
RETURN
    ADDCOLUMNS (
        CROSSJOIN ( TEMPTABLE; VALUES ( Products[Product] ) );
        "SUM"; IF (
            ISBLANK ( CALCULATE ( SUM ( Products[Sum] ) ) );
            0;
            CALCULATE ( SUM ( Products[Sum] ) )
        )
    )

Let me know if works or are close to the solution

Regards

Victor

[Greg_Deckler]

Brilliant @Vvelarde, you always bail me out man. Took me a second to understand why that worked exactly but I get it. So I basically had the right idea, but this is a super slick implementation! Thanks!! If you're in Seattle in about week, I owe you a beverage!