Forum Discussion
How to Create Custom Rows of Aggregated Data
- 2 years ago
Not part of the standard visual functionality. Consider using separate visuals.
- Anonymous2 years ago
Hi, Nashville1986
Thanks for lbendlin reply. You can refer to the following DAX.Create a Table:
User this DAX to the matrix:Sales counts = VAR _JanuaryMonthStartDate = MIN ( 'Table'[Date] ) VAR _JuneMonthEndDate = EOMONTH ( _JanuaryMonthStartDate, 5 ) VAR _JulyMonthStartDate = EOMONTH ( _JuneMonthEndDate, 5 ) + 1 VAR _DecemberMonthEndDate = EOMONTH ( _JanuaryMonthStartDate, 11 ) VAR _secondYearJanuaryStartDate = EOMONTH ( _JanuaryMonthStartDate, 11 ) + 1 VAR _maxDate = MAX ( 'Table'[Date] ) VAR _FirstYear1To6Months = CALCULATE ( SUM ( 'Table'[Sales Count] ), FILTER ( 'Table', 'Table'[Date] >= _JanuaryMonthStartDate && 'Table'[Date] <= _JuneMonthEndDate ) ) VAR _FirstYear7To12Months = CALCULATE ( SUM ( 'Table'[Sales Count] ), FILTER ( 'Table', 'Table'[Date] >= _JulyMonthStartDate && 'Table'[Date] <= _DecemberMonthEndDate ) ) VAR _AfterFirstYearMonths = CALCULATE ( SUM ( 'Table'[Sales Count] ), FILTER ( 'Table', 'Table'[Date] >= _secondYearJanuaryStartDate && 'Table'[Date] <= _maxDate ) ) VAR _AllMonths = CALCULATE ( SUM ( 'Table'[Sales Count] ), FILTER ( 'Table', 'Table'[Date] >= _JanuaryMonthStartDate && 'Table'[Date] <= _maxDate ) ) VAR _categories = SELECTEDVALUE ( 'Table 2'[Categories] ) VAR _result = SWITCH ( _categories, "First Year 1-6 Months", _FirstYear1To6Months, "First Year 7-12 Months", _FirstYear7To12Months, "After First Year Months", _AfterFirstYearMonths, "All Months", _AllMonths ) RETURN _result
Best Regards,
Yang
Community Support TeamIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!How to get your questions answered quickly -- How to provide sample data in the Power BI Forum
Not part of the standard visual functionality. Consider using separate visuals.
- Nashville19862 years agoFrequent Visitor
Can you elaborate? So if the visual doesn't have the functionality then I would need to import another facts/data table with the aggregated date values and then have a separate visual referencing just that table. Is that a correct assumption? Thanks for the help!