Using measures to return data grouped by specific date ranges

[Alex_Ooi]

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 = CALCULATE([# Bookings],
    ALLEXCEPT(Sales,Sales[Site Name],dCalendar[Calendar Date]))

By filtering my date ranges on the slicer to be the same with the campaign period, I would expect the first row to return a result of 4,354.

 

My challenge is to group the booking counts (that contains booking dates in Sales table) into Campaign Start Date and Campaign End Date (which are from the Promo table). I tried writing this but it returns an error for me.

Total Bookings by Campaign Period = CALCULATE([# Bookings],
    ALLEXCEPT(Sales,Sales[Site Name],
        DATESBETWEEN(Sales[Booking Date], PromoCodes[Booking From], PromoCodes[Booking To]))

 Below is the error message:

"A single value for column 'Booking From' in table 'PromoCodes' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

I have no idea why RELATED does not work in my formula above either. After many rounds of research, I am still not able to find light at the end of the DAX tunnel.

Please advise. Thanks.

[v-lili6-msft]

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

[Alex_Ooi]

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]

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]

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]

hi   @Alex_Ooi 

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

Regards,

Lin

[Alex_Ooi]

@v-lili6-msft 

Please find the links below for your review.

PromoCodes Dummy Data , Sales Dummy Data , Pbix Test File 

Besides this issue, I also noticed that my date hierarchy feature is missing. I raised it up in another thread, but I still haven't received reply. Can you please help me out with that also?

[v-lili6-msft]

hi  @Alex_Ooi 

Based on my test on your sample pbix file, do you mean that just the first row will show the data, if so, you could just use this simple formula as below:

Measure = IF(SELECTEDVALUE(PromoCodes[Booking From])=MIN('Calendar'[Date])&&SELECTEDVALUE(PromoCodes[Booking To])=MAX('Calendar'[Date]),[NF # Bookings by Site])

Result:

and for why your date hierarchy feature is missing, just enable it as below:

Result:

Regards,

Lin

[Alex_Ooi]

Oh dear heavens, @v-lili6-msft thank you for figuring out what was wrong with my date hierarchy! That's amazing!!

To reply to your first question, no I am expected to populate the whole field, and not just the first row. I was only able to demonstrate the "reconciliation" using date filter for the first row since different campaigns run on different dates.

Please advise.

[Alex_Ooi]

Hi @v-lili6-msft can I check with you if my reply to you on your question was clear? I am still hoping that someone can provide a solution.

[Alex_Ooi]

@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!

[amitchandak]

Now sure how you dates related, ideally to the campaign end date, based on what you want

Something like this should be the formula if booking dates are not from calendar and calendar is joined to campaign end date

Overall # Bookings = 
var _max = maxx(Bookings,Bookings[Date])
var _min = minx(Bookings,Bookings[Date])
CALCULATE([# Bookings],Filter(all(dCalendar),dCalendar[Calendar Date]<=_max && dCalendar[Calendar Date]<=_min)

 Can you please explain with an example. Provide sample data and sample output

[Alex_Ooi]

Hi @amitchandak ,

Since my data are all connected to my dB via ODBC, I am not able to share a sample file to you. Allow me to share the ERD to you here.

The Sales table is related to Calendar table using dates

The Sales table is related to PromoCodes table using Confirmation Reference ID

After reading your explanation, there is a high chance I may need to establish a relationship between PromoCodes to Calendar table. Please advise.

[amitchandak]

@Alex_Ooi ,

We can work on a few formulae without joining. But we may have to use a cross filter to remove join from sales when we want to run a report on the campaign/promo codes.

1. Do we want to run report that in this range how many campaigns and what are the count of sales or Amount

2. When we in this period does this mean Started in that period or both started or ended in this report.

I actually want to take the current employee formula and use it: https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

I will move my filter part on the promo Code table. I will keep the cross filter to remove the join of sales and date.

Now the count of the employee was coming from the same table. This will not allow me to add any column from sales. So we Will use exiting measures on booking. As promo code is master, it should allow me to use measure from the child table.

[Alex_Ooi]

@amitchandak thank you so much on the feedback. I can finally feel that we're chasing some rabbits here!

To answer your questions:

1. There are multiple campaigns run by multiple "Sites" (see my first attachment). I am supposed to be coding other measures for sales amount etc, but I am confident once I understood how to calculate for total no of bookings, I can do the rest myself.
2. The period should reflect both "Campaign Start Date" and "Campaign End Date" (again, an example is in my first attachment where I circled the date range slicer to be the same with the campaign period.

You mentioned;

"I will move my filter part on the promo Code table. I will keep the cross filter to remove the join of sales and date.

Now the count of the employee was coming from the same table. This will not allow me to add any column from sales. So we Will use exiting measures on booking. As promo code is master, it should allow me to use measure from the child table."

I have never used CROSSFILTER before in DAX, could you help to explain further how could it help in my situation? I have taken a quick read on your post on HR Analytics, but the code is a little to complex for me to understand. Would really appreciate if you could help to break it down for me.

[vivran22]

Hello @Alex_Ooi 

Can you share a sample dataset for this?

Regards,

Vivek

[Alex_Ooi]

@vivran22 Unfortunately I could not because they are connected to my dB via ODBC.