Forum Discussion
New Table - Calendar and Summarize
Hi paul_luvaglia ,
I think Greg_Deckler ‘s solution is great!! just change the countrows to the aggregation you need depending on your needs. If it's just following the sample data you gave, you could probably just create a simple calculated column:
Column = IF([Qty]=BLANK(),[Qty]+0,[Qty])An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
paul_luvaglia Sorry, got pulled away yesterday. If all you have is a single table, then you could do this tweak to the original formula. PBIX is attached below signature.
Table 2 = VAR __Years = DISTINCT('Table'[Year]) VAR __Months = GENERATESERIES( MIN('Table'[Month]), MAX('Table'[Month]), 1 ) VAR __Parts = DISTINCT('Table'[Part]) VAR __Table = ADDCOLUMNS( CROSSJOIN( CROSSJOIN( __Parts, __Years ), __Months ), "__Count", VAR __Part = [Part] VAR __Year = [Year] VAR __Month = [Value] VAR __Result = SUMX( FILTER( 'Table', [Part] = __Part && [Year] = __Year && [Month] = __Month ), [Qty]) + 0 RETURN __Result ) VAR __Result = SELECTCOLUMNS( __Table, "Part", [Part], "Year", [Year], "Month", [Value], "Count", [__Count] ) RETURN __Result
This would be a quick example of the source
| Year | Month | Inv Num | Part | Qty |
| 2024 | 1 | 1 | ABC | 4 |
| 2024 | 2 | 2 | ABC | 6 |
| 2024 | 2 | 3 | XYZ | 4 |
| 2024 | 4 | 4 | ABC | 1 |
I would like some thing as per the below
| Year | Month | Part | Qty |
| 2024 | 1 | ABC | 4 |
| 2024 | 2 | ABC | 6 |
| 2024 | 3 | ABC | 0 |
| 2024 | 4 | ABC | 1 |
| 2024 | 1 | XYZ | 0 |
| 2024 | 2 | XYZ | 0 |
| 2024 | 3 | XYZ | 4 |
| 2024 | 4 | XYZ | 0 |
Hi paul_luvaglia ,
I think Greg_Deckler ‘s solution is great!! just change the countrows to the aggregation you need depending on your needs. If it's just following the sample data you gave, you could probably just create a simple calculated column:
Column = IF([Qty]=BLANK(),[Qty]+0,[Qty])An attachment for your reference. Hope it helps!
Best regards,
Community Support Team_ Scott ChangIf this post helps then please consider Accept it as the solution to help the other members find it more quickly.
- Greg_Deckler
Community Champion
paul_luvaglia Sorry, got pulled away yesterday. If all you have is a single table, then you could do this tweak to the original formula. PBIX is attached below signature.
Table 2 = VAR __Years = DISTINCT('Table'[Year]) VAR __Months = GENERATESERIES( MIN('Table'[Month]), MAX('Table'[Month]), 1 ) VAR __Parts = DISTINCT('Table'[Part]) VAR __Table = ADDCOLUMNS( CROSSJOIN( CROSSJOIN( __Parts, __Years ), __Months ), "__Count", VAR __Part = [Part] VAR __Year = [Year] VAR __Month = [Value] VAR __Result = SUMX( FILTER( 'Table', [Part] = __Part && [Year] = __Year && [Month] = __Month ), [Qty]) + 0 RETURN __Result ) VAR __Result = SELECTCOLUMNS( __Table, "Part", [Part], "Year", [Year], "Month", [Value], "Count", [__Count] ) RETURN __Result