Forum Discussion

Greg_Deckler's avatar
Greg_Deckler
Community Champion
8 years ago
Solved

"Add" a missing row

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

20181CW10201801
201712CW20201712
201711CW15201711
20181XD20201801
201812XD30201812

 

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

20181CW10201801
201712CW20201712
201711CW15201711
20181XD20201801
201712XD30

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

201711201711
201712201712
20181201801

 

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.

 

 

 

 

 

  • 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

2 Replies

  • Vvelarde's avatar
    Vvelarde
    Community Champion

    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's avatar
      Greg_Deckler
      Community Champion

      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!