Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to Solution.
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
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
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!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
99 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |