Forum Discussion
Dynamic Measure that is based on a slicer
Hi kimstriebel ,
Thank you for reaching out to the Microsoft Community Forum and for providing the sample data.Based on your scenario, I've outlined a step-by-step approach that I followed to achieve the desired output:
Step1. Created a Date table
DateTable = CALENDAR(MIN(Data[Valued Date]), MAX(Data[Valued Date]))
Then I marked it as a date table and related DateTable[Date] to Data[Valued Date] in the model.Step 2 Added a date slicer
Used DateTable[Date] in the slicer and set it to Between mode so I could pick two specific dates.Step3. Created these measures to capture the selected min and max dates:
SelectedMinDate = MIN('DateTable'[Date])
SelectedMaxDate = MAX('DateTable'[Date])
Step4. Then created these two measures to get the sales per branch on those selected dates:
Sales_MinDate =
VAR _MinDate = [SelectedMinDate]
RETURN
CALCULATE(
SUM(Data[Rpt Sales]),
FILTER(Data, Data[Valued Date] = _MinDate && Data[Branch] = MAX(Data[Branch]))
)Sales_MaxDate =
VAR _MaxDate = [SelectedMaxDate]
RETURN
CALCULATE(
SUM(Data[Rpt Sales]),
FILTER(Data, Data[Valued Date] = _MaxDate && Data[Branch] = MAX(Data[Branch]))
)
Step5. To calculate the change:
Sales_Change = [Sales_MaxDate] - [Sales_MinDate]
Step6. Added a matrix visual:Rows: Branch
Values: Sales_MinDate, Sales_MaxDate, Sales_Change
If this solution helped resolve your query, kindly mark it as Accepted and consider giving a Kudos so it can assist others in the community facing similar issues.
Let me know if you need further assistance!
Thanks & Regards,
Lakshmi Narayana
Hi, they are dates. Shown in my data as MM/DD/YYYY. I cannot post the original data source do to privacy concerns, but I have made a mock up on a smaller scale of what I am trying to accomplish. Ideally I want to use my date (valued date) as a slicer so that I can select the two dates I am trying to compare, and then view the change in "Sales" across the two dates for each branch.
I also cannot seem to figure out how to attach an excel document in this forum so I've attached a screenshot.
This is generally what my data looks like
In excel I am able to get something close to what I am going for using nested pivot tables, I connect a slicer to the pivot and then have a forumal tracking the change for the selected dates and then another pivot (the one shown below) shows me the change in "reported" for the specific "branch" and I can sort on the largest changes on that period.
Thanks!
Community SupportHi kimstriebel ,
Thank you for reaching out to the Microsoft Community Forum and for providing the sample data.
Based on your scenario, I've outlined a step-by-step approach that I followed to achieve the desired output:
Step1. Created a Date table
DateTable = CALENDAR(MIN(Data[Valued Date]), MAX(Data[Valued Date]))
Then I marked it as a date table and related DateTable[Date] to Data[Valued Date] in the model.
Step 2 Added a date slicer
Used DateTable[Date] in the slicer and set it to Between mode so I could pick two specific dates.
Step3. Created these measures to capture the selected min and max dates:
SelectedMinDate = MIN('DateTable'[Date])
SelectedMaxDate = MAX('DateTable'[Date])
Step4. Then created these two measures to get the sales per branch on those selected dates:
Sales_MinDate =
VAR _MinDate = [SelectedMinDate]
RETURN
CALCULATE(
SUM(Data[Rpt Sales]),
FILTER(Data, Data[Valued Date] = _MinDate && Data[Branch] = MAX(Data[Branch]))
)
Sales_MaxDate =
VAR _MaxDate = [SelectedMaxDate]
RETURN
CALCULATE(
SUM(Data[Rpt Sales]),
FILTER(Data, Data[Valued Date] = _MaxDate && Data[Branch] = MAX(Data[Branch]))
)
Step5. To calculate the change:
Sales_Change = [Sales_MaxDate] - [Sales_MinDate]
Step6. Added a matrix visual:
Rows: Branch
Values: Sales_MinDate, Sales_MaxDate, Sales_Change
If this solution helped resolve your query, kindly mark it as Accepted and consider giving a Kudos so it can assist others in the community facing similar issues.
Let me know if you need further assistance!
Thanks & Regards,
Lakshmi Narayana
Amazing! Worked perfectly. Thank you so much!