Forum Discussion
"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
| 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 MyDataTableThis 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.
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
- VvelardeCommunity Champion
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_DecklerCommunity 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!