I have two dates: one is a daily date used for the numerator calculation, and the other is a monthly date used for the denominator calculation. These two dates are concatenated into a single date, which is a combination of daily and monthly data. However, the year, month, and week generated from this combined date are not showing correctly for the denominator since it's monthly data i.e 1 st of each month , it's only appearing in week 1. I want the data to be split into weeks based on the month weeks, and the total should be the sum of the weekly data, like how the numerator is handled. The measure value is calculated as sum(numerator) / sum(denominator).
Daily date ex-01/01/2024,02/01/2024,03/01/2024..........
Monthly date ex- 01/01/2024,01/02/2024,01/03/2024......
The issue you are facing is related to how the combined date field is interpreted when calculating the week and month values. Since the denominator date is a monthly date (e.g., 01/01/2024, 01/02/2024, etc.), it is treated as the first day of each month, which falls into week 1 of that month.
To split the monthly data into weeks and calculate the total as the sum of weekly data, you can create a separate week field for the denominator date. This can be achieved by using a date function that calculates the week number based on the month and year, rather than the day of the month.
One approach is to create a calculated field or measure that extracts the month and year from the denominator date, and then calculates the week number based on those values. For example, you could use a combination of the `month()` and `year()` functions to extract the month and year, and then use the `weekstart()` function to calculate the week number for each week in that month.
By creating a separate week field for the denominator, you can then aggregate the denominator values by this new week field, effectively splitting the monthly data into weeks. The total can then be calculated as the sum of these weekly denominator values.
This approach ensures that the denominator data is correctly distributed across the weeks within each month, aligning with how the numerator data is handled on a daily basis.
