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
v-lili6-msft's avatar
v-lili6-msft
Community Support
6 years ago

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

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

    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.