Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi Power BI folks,
I have been trying to solve this problem for a while. What if you wanted to not create a YTD line chart but a chart that starts at a custom period and ends at a custom period. For this use case I created a between slicer and added a line chart with months on the X-axis and this formula on the Y-axis:
VAR minDate = CALCULATE(MIN(D_Dates[Date]), ALL()) // Should ignore the X-axis filter
VAR currentDate = CALCULATE(MAX(D_Dates[Date])) // Should use the X-axis filter
VAR totalAmount = CALCULATE(SUM(F_SalesOrderLines[TotalAmount]), FILTER(ALL(D_Dates), D_Dates[Date]>=minDate && D_Dates[Date]<=currentDate))
RETURN totalAmount
The problem is: The minDate variable should be calculated while ignoring the filters of the X-axis.
In some situations I managed to get it working by creating a copy of the date table (D_Date_Alternative) which then is used as the X-axis. Which allows me to ignore the X-axis using the ALL() DAX function.
Is there a proper way to filter "visualisation field level filters"out? Without making my datamodel overly complex.
Kind regards,
Rik
@Riktastic
I am not sure if I understand your question correctly. To create a cumulative sales based on a custom start and end date with a model where you have a single dates tables connected to a fact table, you follow this mesure. I have used ALL here so the the starting value is accumualted since the date when values are avilable, you can change it to ALLSELECTED to start the values to acumilate from the the minimum date. This ignores filters applied to the dates table frrom any place on your report.
**bleep** Sales =
VAR __MinDate = CALCULATE( MIN('Calendar'[Date]) , all( 'Calendar' ) )
RETURN
CALCULATE(
SUM(Sales[Value] ),
'Calendar'[Date] >= __MinDate,
'Calendar'[Date] <= MAX('Calendar'[Date])
)
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hi Fowmy, thank you for your reply.
The problem that I'm having is that I want to filter on the values of the slicer. Ignoring any filters on the visual.
For example: If I create a line chart and add months to the X-axis, the MIN(Date) and MAX(Date) of January will be 01/01/2023 and 31/01/2023. My slicer has been filtered to show values between 01/01/2022 and 01/01/2024. These are the values I want to access.
User | Count |
---|---|
121 | |
72 | |
71 | |
57 | |
50 |
User | Count |
---|---|
167 | |
83 | |
68 | |
66 | |
55 |