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.
I am trying to create a measure that calculates total sales within a range of selected dates that are calculated within the measure. The table groups sales by the latest possible date in the range. Here is the measure:
SalesInRange =
VAR
MaxDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),0)
VAR
MinDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),-[Variable])
RETURN
CALCULATE(SUM(Sales[Sales]), FILTER(ALLEXCEPT(Test2, Test2[BranchCode]), Test2[DateofSale]<=MaxDate && Test2[DateofSale]>MinDate && Test2[NewRenew]="R"))
The variable measure is the number of months that determines when to start the date range.
The problem is that I want the measure to filter out renewed sales within the calculation, but I wanted the branch office to be able to be selected in a filter on the dashboard. If I change the calculation to:
CALCULATE(SUM(Sales[Sales]), ALL(Sales),Sales[DateofSale]<=MaxDate,Sales[DateofSale]>MinDate, Sales[NewRenew]="R", Sales[BranchCode]="A")
where the branch is filtered within the measure, it works fine. But I do not want to filter within the measure I want to slice it on the dashboard. Any help would be appreciated. Thanks!
Solved! Go to Solution.
Hello @Anonymous, I am not totally sure if I get the question, but I put in all combinations I think you might need. Let me know if I still didn't ans your question.
If you want to find sales per branch no matter what filter is appliedthen you can use this formula:
SalesTest1 = VAR MaxDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),0) VAR MinDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),-[Variable]) RETURN CALCULATE(SUM(Sales[Sale]), FILTER(ALL(Sales),Sales[DateofSale]<=MaxDate && Sales[DateofSale]>MinDate && Sales[NewRenew]="R"),ALLEXCEPT(Sales,Sales[Branch]))
if you want it driven by a filter then this:
SalesTest1 = VAR MaxDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),0) VAR MinDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),-[Variable]) RETURN CALCULATE(SUM(Sales[Sale]), FILTER(ALLSELECTED(Sales),Sales[DateofSale]<=MaxDate && Sales[DateofSale]>MinDate && Sales[NewRenew]="R"))
@Anonymous Can you create a mock data file and post it here? I am just having some trouble understanding what you want to do. If you can post some sample data sets and results I can probably give you the formulae.
Hello @Anonymous, I am not totally sure if I get the question, but I put in all combinations I think you might need. Let me know if I still didn't ans your question.
If you want to find sales per branch no matter what filter is appliedthen you can use this formula:
SalesTest1 = VAR MaxDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),0) VAR MinDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),-[Variable]) RETURN CALCULATE(SUM(Sales[Sale]), FILTER(ALL(Sales),Sales[DateofSale]<=MaxDate && Sales[DateofSale]>MinDate && Sales[NewRenew]="R"),ALLEXCEPT(Sales,Sales[Branch]))
if you want it driven by a filter then this:
SalesTest1 = VAR MaxDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),0) VAR MinDate = EOMONTH(SELECTEDVALUE(Date_Dimension[MonthYear]),-[Variable]) RETURN CALCULATE(SUM(Sales[Sale]), FILTER(ALLSELECTED(Sales),Sales[DateofSale]<=MaxDate && Sales[DateofSale]>MinDate && Sales[NewRenew]="R"))
The second solution of getting the measure driven by the filter is what I needed. Thank you so much for your help!
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 |
---|---|
114 | |
99 | |
82 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |