Forum Discussion
Dynamic Measure that is based on a slicer
Hi!
I have been working on this problem for awhile and could use some experT help.
I am trying to look at how our total sales changed between two dates for specific branchs. I am trying to use Power bi to have a date slicer in which you input the two dates you want to compare, and then a matrix showing branch offices as the row and then the sales change between those two dates in the columns.
Im able to do this easily in excel, where I created a pivot table, filtering on the selected dates in the table using a slicer, then a formula calucates the change, and then I use that table as an additional pivot table so I can filter it down further and sort.
Is there any way to do this in Power bi using DAX?
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
4 Replies
- danextian
Super User
Hi kimstriebel
When you say two dates, are those really dates or two periods (months, quarter, year)?
Please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.
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!
v-lgarikapatCommunity Support
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 NarayanaAmazing! Worked perfectly. Thank you so much!
