Forum Discussion
Using measures to return data grouped by specific date ranges
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.
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.
amitchandak thank you so much on the feedback. I can finally feel that we're chasing some rabbits here!
To answer your questions:
- 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.
- 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.