I've attached the following here. This was done under the assumption that the same population value would appear for each day in the month as that's how I read your comment.
I then used a combination of Window calculations to perform the task at hand. Let me know if this works!
Month-Quarter Aggregation.twbx 19.3 KB
Thank you very much. this worked for me. Can you please tell me how did this calculation resulted the correct value? Thanks, Susheela
I can try! Here's the same workbook with the final calc having parts _1 and _2.
This will return 420,000 for all April dates. Regardless if showing all 3 days or just the monthly view (thanks to the LOD calculation already done). This LOD was important as to not inflate the population if, for instance, May had 4 dates in the dataset vs. April's 3. Now we'll always have one row for a month behind the scenes. When you're looking at it by year, there's still the summing of the different months occurring.
Thankfully you already had this in there! You wanted to count the number of months in the range or year, so it's a count distinct (April = 1, May = 1), but then summed for the entire view (2, in this case).
Hopefully this makes sense! Hardest part is then determine what to "Compute Using" ("Cell" in this case).
Month-Quarter Aggregation.twbx 17.2 KB