Dynamic Measure that is based on a slicer

[kimstriebel]

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? 

[kimstriebel]

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-lgarikapat · Accepted solution]

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

[kimstriebel]

Amazing! Worked perfectly. Thank you so much! 

[danextian]

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.