Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello everyone, I am new to the PBI and have been playing with some data. I have a table with fields; Products like A, B, C, etc., SubCategory like AA, AB, AC, etc., Date and Quantity which is cumulative for every combination of Product and Subcategory based on the Date.
Product | SubCategory | Date | Qt |
A | AA | 4/1/2020 | 5 |
A | AA | 4/2/2020 | 9 |
A | AA | 4/3/2020 | 11 |
A | AA | 4/4/2020 | 17 |
A | AA | 4/5/2020 | 25 |
A | AB | 4/1/2020 | 5 |
A | AB | 4/2/2020 | 8 |
A | AB | 4/3/2020 | 12 |
A | AB | 4/4/2020 | 20 |
A | AB | 4/5/2020 | 21 |
A | AC | 4/1/2020 | 2 |
A | AC | 4/2/2020 | 6 |
A | AC | 4/3/2020 | 15 |
For each date, I want to calculate the difference between Qt on any date and Qt on the date before that date (We also need to take an account that they are cumulative sum). For example:
Date | Difference |
4/1/2020 | 5+5+2 = 12 |
4/2/2020 | (9+8+6)-(12)=11 |
I tried several ways but could not succed. Can anyone help me on this?
Solved! Go to Solution.
In case you want it, here is a more complete solution using a Date table to get both the daily difference and the cumulative daily difference...
Date Table
Date =
VAR Dates =
CALENDAR (
DATE ( YEAR ( MIN ( 'Sales'[Date] ) ), 1, 1 ),
DATE ( YEAR ( MAX ( 'Sales'[Date] ) ), 12, 31 )
)
RETURN
ADDCOLUMNS (
Dates,
"Year", YEAR ( [Date] ),
"Quarter", FORMAT ( [Date], "q" ),
"Month", MONTH ( [Date] ),
"Month_Name", FORMAT ( [Date], "mmmm" ),
"Month_Short_Name", FORMAT ( [Date], "mmm" ),
"Year_Quarter", YEAR ( [Date] ) & "-Q"
& FORMAT ( [Date], "q" ),
"Year_Month", YEAR ( [Date] ) & "-"
& FORMAT ( [Date], "mm" )
)
DatesWithsales Column
DatesWithSales = 'Date'[Date] <= MAX( 'Sales'[Date])
Total_Date_Sales Measure
Total_Date_Sales = SUMX ( RELATEDTABLE(Sales), [Total_Qt])
Sales Table Measures
Total_Qt = SUM(Sales[Qt])
Cum_Qt = // Running Total //
VAR LastVisibleDate = MAX('Sales'[Date])
RETURN
CALCULATE(
SUM( Sales[Qt]),
FILTER(
ALL( 'Sales'),
Sales[Date] <= LastVisibleDate)
)
Cum_Daily_Qt_Diff =
VAR Curr_Date =
SELECTEDVALUE ( 'Date'[Date] )
VAR Prev_Date = Curr_Date - 1
RETURN
CALCULATE ( [Cum_Qt], DATESBETWEEN ( 'Date'[Date], Prev_Date, Curr_Date ) )
- CALCULATE ( [Cum_Qt], DATESBETWEEN ( 'Date'[Date], Prev_Date, Prev_Date ) )
Daily_Qt_Diff =
VAR Curr_Date =
SELECTEDVALUE ( 'Date'[Date] )
VAR Prev_Date = Curr_Date - 1
RETURN
CALCULATE ([Total_Date_Sales], DATESBETWEEN ( 'Date'[Date], Curr_Date, Curr_Date ) )
- CALCULATE ( [Total_Qt], DATESBETWEEN ( 'Date'[Date], Prev_Date, Prev_Date ) )
While having a date table is best pratice, this solution does not require one.
Measures are...
Total_Qt = SUM(Product_Sales[Qt])
QT_RT = //RT = Running Total
VAR LastVisibleDate = MAX('Product_Sales'[Date])
RETURN
CALCULATE(
SUM( Product_Sales[Qt]),
FILTER(
ALL( 'Product_Sales'),
Product_Sales[Date] <= LastVisibleDate)
)
Diff =
VAR Curr_Date = SELECTEDVALUE('Product_Sales'[Date])
VAR Prev_Date = Curr_Date - 1
RETURN
CALCULATE([QT_RT], DATESBETWEEN('Product_Sales'[Date], Prev_Date, Curr_Date ) )
- CALCULATE([QT_RT], DATESBETWEEN('Product_Sales'[Date], Prev_Date, Prev_Date ) )
Let me know if this worked for you. Thanks
In case you want it, here is a more complete solution using a Date table to get both the daily difference and the cumulative daily difference...
Date Table
Date =
VAR Dates =
CALENDAR (
DATE ( YEAR ( MIN ( 'Sales'[Date] ) ), 1, 1 ),
DATE ( YEAR ( MAX ( 'Sales'[Date] ) ), 12, 31 )
)
RETURN
ADDCOLUMNS (
Dates,
"Year", YEAR ( [Date] ),
"Quarter", FORMAT ( [Date], "q" ),
"Month", MONTH ( [Date] ),
"Month_Name", FORMAT ( [Date], "mmmm" ),
"Month_Short_Name", FORMAT ( [Date], "mmm" ),
"Year_Quarter", YEAR ( [Date] ) & "-Q"
& FORMAT ( [Date], "q" ),
"Year_Month", YEAR ( [Date] ) & "-"
& FORMAT ( [Date], "mm" )
)
DatesWithsales Column
DatesWithSales = 'Date'[Date] <= MAX( 'Sales'[Date])
Total_Date_Sales Measure
Total_Date_Sales = SUMX ( RELATEDTABLE(Sales), [Total_Qt])
Sales Table Measures
Total_Qt = SUM(Sales[Qt])
Cum_Qt = // Running Total //
VAR LastVisibleDate = MAX('Sales'[Date])
RETURN
CALCULATE(
SUM( Sales[Qt]),
FILTER(
ALL( 'Sales'),
Sales[Date] <= LastVisibleDate)
)
Cum_Daily_Qt_Diff =
VAR Curr_Date =
SELECTEDVALUE ( 'Date'[Date] )
VAR Prev_Date = Curr_Date - 1
RETURN
CALCULATE ( [Cum_Qt], DATESBETWEEN ( 'Date'[Date], Prev_Date, Curr_Date ) )
- CALCULATE ( [Cum_Qt], DATESBETWEEN ( 'Date'[Date], Prev_Date, Prev_Date ) )
Daily_Qt_Diff =
VAR Curr_Date =
SELECTEDVALUE ( 'Date'[Date] )
VAR Prev_Date = Curr_Date - 1
RETURN
CALCULATE ([Total_Date_Sales], DATESBETWEEN ( 'Date'[Date], Curr_Date, Curr_Date ) )
- CALCULATE ( [Total_Qt], DATESBETWEEN ( 'Date'[Date], Prev_Date, Prev_Date ) )
Create measures like this with Date Calendar dimension
Total QT =sum(Table[QT])
last QT= CALCULATE([Total QT],Dateadd(Date[DAte],-1,Day))
Diff = [Total QT] -[last QT]
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
User | Count |
---|---|
76 | |
75 | |
46 | |
31 | |
27 |
User | Count |
---|---|
99 | |
91 | |
51 | |
48 | |
47 |