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
v-lili6-msft thank you for the tutorial. In my case, I am using values between 3 tables, which I suppose being the major reason of the difficulty I am facing now. I will try to merge my queries and attempt your solution. Hope it works!
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?
