Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi all! I have an issue with creating a report and will be thankful for any help.
I have two tables:
1) Data
Date Id AmountUSD Type
2019-10-01 111 1000 1
2019-10-01 112 10 0
2019-10-02 113 500 0
2019-10-03 114 400 0
2) DateTimeTable
Actually, datetimetable with one column Date with distinct values for dates
I have a date slicer and two card visuals: one for the sum of all amounts from date before the selected and second for the difference between sum of all amounts with type 0 between selected date and date before the selected.
I'm using following measure:
Sum of all amounts from date before the selected =
Sum of all amounts from date before the selected with type 0 =
Sum of all amounts from selected date with type 0 =
Solved! Go to Solution.
thanks a lot! it seems I finally found a solution. now I'm calculating value for the day BEFORE THE SELECTED like that:
You have mentioned
"2) DateTimeTable
Actually, date timetable with one column Date with distinct values for dates
"
Make sure your date timetable is continuous table else use power BI calendar command to generate the calendar.
Try to take a max of the selected value. This should even work without selected value.
Var _end_date=(maxx('Date','Date'[Date]))
Use this in =.
Also, test the data without date filter check that another filter is working fine.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
Is it possible to share sample pix after removing sensitive information?
thanks a lot! it seems I finally found a solution. now I'm calculating value for the day BEFORE THE SELECTED like that:
By the way, you may help accept solution. Your contribution is highly appreciated.
https://www.sqlbi.com/articles/filter-arguments-in-calculate/
@amitchandak wrote:You have mentioned
"2) DateTimeTable
Actually, date timetable with one column Date with distinct values for dates
"
Make sure your date timetable is continuous table else use power BI calendar command to generate the calendar.
Try to take a max of the selected value. This should even work without selected value.
Var _end_date=(maxx('Date','Date'[Date]))
Use this in =.
Also, test the data without date filter check that another filter is working fine.
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.
Yep, I'm using calendar command for creating date tables.
Try to take a max of the selected value. This should even work without selected value. - Could you please explain this? Cause I'm receiving an error "too few argument were passed to MAXX function"
yep, I've tested my filter, both filters works perfectly without each other)
ok, I managed to solve this by changing date slicer from datetimetable to data table. So now I'm slicering by date column in Data Table. My final query looks like:
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
85 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |