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 = 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.

Need Help
Tutorial Requests

16 Replies

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

    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's avatar
      Alex_Ooi
      Helper IV
      6 years ago

      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!

    • 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

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    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's avatar
      Alex_Ooi
      Helper IV
      6 years ago

      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's avatar
        amitchandak
        Super User
        6 years ago

        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-trend/ba-p/882970

         

        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.