Forum Discussion
Tricky Month-Year Level Aggregated Total
I just opened the pbix file. I think the issue is with your DAX that you have. For example, 01 Schedule Trans - this DAX is identifying the latest report date and is then filtering to include only rows where ReportDate from your dim table is one week before the report date and the TransScheduleCompletionDate is on or before the identified report date, but one week before the report date. Then, it counts the rows where this criteria is met. So, it will always only return the latest week.
Right, that was the requirement, we need to calculate scheduletrans at weekly level and then we have to show aggregated sum for weeks in that month
Hi! I played around with one of your measures and got it to work. You should be able to apply similar logic to the rest. In the below sceenshot, your original measure of completetrans is showing and Measure2 is the one I adjusted and it returns the count at the row level, but the total is the sum of those counts.
Measure2 =
VAR _reportdate = MAX(ReportDateDimension[ReportDate])
VAR _filteredData =
FILTER(
Data,
Data[TransActualCompletionDate] <= _reportdate &&
Data[TransActualCompletionDate] > _reportdate - 7
)
RETURN
SUMX(
VALUES(ReportDateDimension[ReportDate]),
CALCULATE(
COUNTROWS(_filteredData),
ReportDateDimension[ReportDate] = EARLIER(ReportDateDimension[ReportDate])
)
)Could you please share the revised .pbix file..
Actually my measure is wrong - it's closer, but wrong. 38 plus 71 = 109, but the measure is showing 142.