Forum Discussion
Calculating the difference between dates in the same column
Hi All,
I am new to power BI.
To explain my request is very simple (Dataset:financial data set from microsoft)
- Create 2 date slicer based on the same "Date" column.
- Display the profits based on the dates slicers selected
- Calculate the difference of profit based on the dates selected and display it in a card
I have attached the visualization below
Could you please help me with the request please ?
Hello Anonymous
We jsut need a couple date tables, one inactive relationship and the right measure.
Notice that the relationship between Dates 2 in the data is inactive. We will turn it on in our mesure when needed. We can get a simple date table with this DAX
Dates 1 = VAR DateRange = CALENDARAUTO() RETURN ADDCOLUMNS( DateRange, "Year",YEAR([Date]), "Month",FORMAT([Date],"mmmm"), "Year Month", FORMAT([Date],"yyyy-mmmm"), "YearMonthSort",YEAR([Date])*100 + MONTH([Date]), "ShortName",FORMAT([Date],"ddd"), "IsWeekDay", NOT WEEKDAY( [Date] ) IN {1,7} )Dates 2 is just this.
Dates 2 = 'Dates 1'
Amount 1 is straight sum using the first date relationship.
Amount 1 = SUM ( Data[Amount] )
Amount 2 is where we turn off the relationship with the first date table and turn on the link with the second one.
Amount 2 = CALCULATE( SUM ( Data[Amount] ), CROSSFILTER ( Data[Date], 'Dates 1'[Date], None), // Turns off the link to Dates 1 USERELATIONSHIP ( Data[Date], 'Dates 2'[Date] ) // Turns on the link to Dates 2 )Then the profit measure
Profit = [Amount 2] - [Amount 1]
And we get out desired result.
I have attached my sample workbook for you to look at.
5 Replies
Hello Anonymous
We jsut need a couple date tables, one inactive relationship and the right measure.
Notice that the relationship between Dates 2 in the data is inactive. We will turn it on in our mesure when needed. We can get a simple date table with this DAX
Dates 1 = VAR DateRange = CALENDARAUTO() RETURN ADDCOLUMNS( DateRange, "Year",YEAR([Date]), "Month",FORMAT([Date],"mmmm"), "Year Month", FORMAT([Date],"yyyy-mmmm"), "YearMonthSort",YEAR([Date])*100 + MONTH([Date]), "ShortName",FORMAT([Date],"ddd"), "IsWeekDay", NOT WEEKDAY( [Date] ) IN {1,7} )Dates 2 is just this.
Dates 2 = 'Dates 1'
Amount 1 is straight sum using the first date relationship.
Amount 1 = SUM ( Data[Amount] )
Amount 2 is where we turn off the relationship with the first date table and turn on the link with the second one.
Amount 2 = CALCULATE( SUM ( Data[Amount] ), CROSSFILTER ( Data[Date], 'Dates 1'[Date], None), // Turns off the link to Dates 1 USERELATIONSHIP ( Data[Date], 'Dates 2'[Date] ) // Turns on the link to Dates 2 )Then the profit measure
Profit = [Amount 2] - [Amount 1]
And we get out desired result.
I have attached my sample workbook for you to look at.
Thanks a lot jdbuchanan71 Anonymous both the Methods are working.
Hi,
I dont understand the reason to have two filters on the same Column. If you are looking to have two filters into the same Page then I would suggest you to do the below,
Make a copy of the Table the table with the required columns and write a measure that would say
SUM(TableCopy1[Profit])- SUM(TableCopy2[Profit]). Basically, you need to have the second Date filter coming from the Second table.
Hi,
I am doubtful if it is possible. I would however provide you with another suggestion.
Create a new Date Table that would contain all the distinct Values of the Dates from the first Table using the below formula.
DATE:= DISTINCT(TableA[Date])
Once the Date Table is Created from the first Table, make sure there is no relationship between the TableA and the Date Table.
VAR First_Date= FIRSTDATE(DATE[Date]) VAR Last_Date=LASTDATE(DATE[Date]) RETURN SUM(TableA[Profit])- SUMX(FILTER(TableA , TableA[Date]>=First_Date && TableA[Date]<= Last_Date),TableA[Profit])