Weekly and MTD totals choosing the month AND whole week.

[alfranco17]

Hi.

I am working on a report that is driving me nuts. I am trying to use a measure TOTAL QTY := TOTALMTD(SUM(Production[Qty]),Calendar[Date]).

The report covers both report whole weeks and MTD. So, December goes from Sunday december 1st to friday january 3rd and January will go from sunday december 29th to friday january 31st.

The ambiguity in that week is my problem.

In this case, there are 6 days that could add up either to december or to january (dec 29 - Jan 3), and I need to compute both MTD total and weekly totals. If I filter by week, it automatically takes the last day and calculates the MTD for January. To get the one for december, I tried using TOTALMTD(SUM(Production[Qty]),ADDDATE(Calendar[Date],-1,MONTH)), but my problem is that the user needs to decide whether to get the total for december or for january, and that's where I got stuck.

I am filtering by weeks. If I try to filter by month, I lose the totals by week. If I filter by week, I cannot get the total for the first month.

Have you had to solve something similar?

Thanks.