Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have a table called "Combined" with a column for "Date". The table is a list of activities. I have built a dates table using dax and set it up as a sicer. It is called "DatesTable" and the column with the dates is called "SelectedDate" I want to be able to select a date from this drop down and then with two measures show the activites 90 days before this date (Before) and 90 days after this date (After).
If I hard code the dates into the mesaure it works just fine ...
Before = CALCULATE(COUNTROWS(Combined), DATESBETWEEN(Combined[Date], Date(2017,1,1), Date(2017,3,31)))
After2 = CALCULATE(COUNTROWS(Combined), DATESBETWEEN(Combined[Date], Date(2016,10,1), Date(2017,1,1)))
But when I try to use the dates from the drop down it doesn't.
Before = CALCULATE(COUNTROWS(Combined), DATESBETWEEN(Combined[Date], 'Dates Table'[SelectedDate], 'Dates Table'[SelectedDate]+90))
After = CALCULATE(COUNTROWS(Combined), DATESBETWEEN(Combined[Date], 'Dates Table'[SelectedDate], 'Dates Table'[SelectedDate]-90))
I tried making the dates from the dates table into measures and dropping them in but that didnt work either. Any suggestions?
Solved! Go to Solution.
I figured it out, when I changed my date selector to the date within the combined table it works. I had it as my date table previously.
Hi @RonHall,
According to what I understand you want to calculate the Before and After based on a slicer you made with the SelectedDate, to make this you need to know what is the selected date so in the dates table make this measure:
DateSelected = Max('Dates Table'[SelectedTable])
Now use this measure in your Before and after should look something like this:
Before = CALCULATE(COUNTROWS(Combined), DATESBETWEEN(Combined[Date], [DateSelected], [DateSelected]+90))
After = CALCULATE(COUNTROWS(Combined), DATESBETWEEN(Combined[Date], [DateSelected], [DateSelected]-90))
Regards
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsThank you for the input, the measures work and the dates change when I select a new date from the drop down, but the bar chart where I have Before and After doesn't. It shows one set of data and does not change when I change the date in the drop down.
Actually it is changing, but the values for before and after are exactly the same every time.
I figured it out, when I changed my date selector to the date within the combined table it works. I had it as my date table previously.
User | Count |
---|---|
98 | |
90 | |
78 | |
71 | |
64 |
User | Count |
---|---|
114 | |
97 | |
95 | |
68 | |
65 |