Forum Discussion

Alex_Ooi's avatar
Alex_Ooi
Helper IV
6 years ago

Using measures to return data grouped by specific date ranges

Hi folks, I want to create a measure called Total Bookings by Campaign Period, which I am stuck at the moment. The closest I could get to this is using the measure below: Overall # Bookings = CALC...
Need Help
Tutorial Requests
Alex_Ooi's avatar
Alex_Ooi
Helper IV
6 years ago

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?

v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

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

  • Alex_Ooi's avatar
    Alex_Ooi
    Helper IV
    6 years ago

    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?

    • v-lili6-msft's avatar
      v-lili6-msft
      Community Support
      6 years ago

      hi   Alex_Ooi 

      You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

       

      Regards,

      Lin