Forum Discussion
Using measures to return data grouped by specific date ranges
hi Alex_Ooi
Since [Campaign Start Date], [Campaign End Date] are different columns in a table, for this case, it usually needs a separate date table (that don't create any active relationship with other tables)as a slicer. see this similar post:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Periodic-Billing/m-p/409365
If you still have the problem, could you please use dummy data to create a simple sample pbix file and share it and your expected output for us have a test.
Regards,
Lin
Hi v-lili6-msft , I have remodelled my data and I believe I am closer to getting my solution now.
I modified the code and here's what I did
Total Bookings by Campaign Period =
VAR tmpCalendar = ADDCOLUMNS(dCalendar, "Date", dCalendar[Calendar Date])
VAR tmpTable =
SELECTCOLUMNS(
FILTER(
GENERATE(
Sales, SUMMARIZE(tmpCalendar,[Date])
),
[Date] >= [Campaign Start Date] &&
[Date] <= [Campaign End Date]
),
"Site Name", Sales[Site Name],
"Booking Date", Sales[Booking Date],
"Booking Ref", Sales[Confirm Ref]
)
RETURN COUNTX(tmpTable, [# Bookings])
For this record, I got 3,300 instead of 4,354. Can you please check what is wrong with my code?
hi Alex_Ooi
Don't create a relationship between 'dCalendar' table and 'sales' table.
and if still have the problem, could you please share a simple sampe pbix file for us have a test?
Regards,
Lin
Hi v-lili6-msft even after following your advise, I am still not able to get the desired output.
Unfortunately, after creating a dummy data, I just realised this forum does not have "ATTACH FILES" feature! Can you please guide me how should I send my data over to you?
hi Alex_Ooi
You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Regards,
Lin