Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hello Everyone, i have a pbix file which i will share that contains expenses table, revenues table, datetable and a sort table. https://1drv.ms/u/s!Ag9tIyk2ofNRjj9fJ_lA-8qzOsre?e=7JJxII
i want to make a table that shows selected values of a date from a slicer and then show me the previous date next to it.
but i use a timeline slicer that has alot of options to choose from ( day,month,year,week,quarter)
my goal is to if i select the Day granuality, and choose for example 30 august 2022, i want to see also the previous day so 29 august 2022, or if i select a range from 20 august and 30 August, i want to be able to see the values from these days and next to it yhe values previous days which is 10 days so from 9 august to 19 august.
if i choose the month granuality and select september, i want to see the previous value selected which is august.
if i choose the week granulaity, and select week 27 , i want to see the selected financial value and i want to also see previous period value which is week 26.
I would appreciate the help
Solved! Go to Solution.
Hi @eliasayy
Please try:
Create two measures to display the selected dates and previous dates:
PreviousDate =
var _min= MIN('DateTable'[Date])
var _Max = MAX('DateTable'[Date])
var _days = COUNTROWS(CALENDAR(_min,_Max))
return IF(_min<>_Max,"From "&_min-_days&" to "&_Max-_days,FORMAT(_min-_days,"YYYY/MM/DD"))
SelectedDate =
var _min= MIN('DateTable'[Date])
var _Max = MAX('DateTable'[Date])
return IF(_min<>_Max,"From "&_min&" to "&_Max,FORMAT(_min,"YYYY/MM/DD"))
Output:
Then create a new measure for previous value:
Previous Financial Value =
VAR _min =
MIN ( 'DateTable'[Date] )
VAR _Max =
MAX ( 'DateTable'[Date] )
VAR _days =
COUNTROWS ( CALENDAR ( _min, _Max ) )
VAR Totalrevenue =
CALCULATE (
SUM ( Revenue[Amount] ),
FILTER ( ALL ( 'Revenue' ), [Date] <= _Max - _days && [Date] >= _min - _days )
)
VAR Payroll =
CALCULATE (
SUM ( Expenses[Amount] ),
FILTER (
ALL ( Expenses ),
Expenses[Category] = "Payroll Expenses"
&& [Date] <= _Max - _days
&& [Date] >= _min - _days
)
)
VAR Business =
CALCULATE (
SUM ( Expenses[Amount] ),
FILTER (
ALL ( Expenses ),
Expenses[Category] = "Business Expenses"
&& [Date] <= _Max - _days
&& [Date] >= _min - _days
)
)
VAR Financial =
CALCULATE (
SUM ( Expenses[Amount] ),
FILTER (
ALL ( Expenses ),
Expenses[Category] = "Financial Fees"
&& [Date] <= _Max - _days
&& [Date] >= _min - _days
)
)
VAR Totalexpense =
CALCULATE (
SUM ( Expenses[Amount] ),
FILTER ( ALL ( 'Expenses' ), [Date] <= _Max - _days && [Date] >= _min - _days )
)
VAR Grossprofit = ( Totalrevenue - Totalexpense )
VAR Taxes = ( Totalrevenue * 0.15 )
VAR Netprofit = Grossprofit - Taxes
VAR Result =
SWITCH (
SELECTEDVALUE ( 'Sort Table'[Index] ),
0, Totalrevenue,
2, Payroll,
4, Business,
6, Financial,
8, Totalexpense,
10, Grossprofit,
12, Taxes,
14, Netprofit
)
RETURN
Result
Apply it to the table visual
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @eliasayy
Please try:
Create two measures to display the selected dates and previous dates:
PreviousDate =
var _min= MIN('DateTable'[Date])
var _Max = MAX('DateTable'[Date])
var _days = COUNTROWS(CALENDAR(_min,_Max))
return IF(_min<>_Max,"From "&_min-_days&" to "&_Max-_days,FORMAT(_min-_days,"YYYY/MM/DD"))
SelectedDate =
var _min= MIN('DateTable'[Date])
var _Max = MAX('DateTable'[Date])
return IF(_min<>_Max,"From "&_min&" to "&_Max,FORMAT(_min,"YYYY/MM/DD"))
Output:
Then create a new measure for previous value:
Previous Financial Value =
VAR _min =
MIN ( 'DateTable'[Date] )
VAR _Max =
MAX ( 'DateTable'[Date] )
VAR _days =
COUNTROWS ( CALENDAR ( _min, _Max ) )
VAR Totalrevenue =
CALCULATE (
SUM ( Revenue[Amount] ),
FILTER ( ALL ( 'Revenue' ), [Date] <= _Max - _days && [Date] >= _min - _days )
)
VAR Payroll =
CALCULATE (
SUM ( Expenses[Amount] ),
FILTER (
ALL ( Expenses ),
Expenses[Category] = "Payroll Expenses"
&& [Date] <= _Max - _days
&& [Date] >= _min - _days
)
)
VAR Business =
CALCULATE (
SUM ( Expenses[Amount] ),
FILTER (
ALL ( Expenses ),
Expenses[Category] = "Business Expenses"
&& [Date] <= _Max - _days
&& [Date] >= _min - _days
)
)
VAR Financial =
CALCULATE (
SUM ( Expenses[Amount] ),
FILTER (
ALL ( Expenses ),
Expenses[Category] = "Financial Fees"
&& [Date] <= _Max - _days
&& [Date] >= _min - _days
)
)
VAR Totalexpense =
CALCULATE (
SUM ( Expenses[Amount] ),
FILTER ( ALL ( 'Expenses' ), [Date] <= _Max - _days && [Date] >= _min - _days )
)
VAR Grossprofit = ( Totalrevenue - Totalexpense )
VAR Taxes = ( Totalrevenue * 0.15 )
VAR Netprofit = Grossprofit - Taxes
VAR Result =
SWITCH (
SELECTEDVALUE ( 'Sort Table'[Index] ),
0, Totalrevenue,
2, Payroll,
4, Business,
6, Financial,
8, Totalexpense,
10, Grossprofit,
12, Taxes,
14, Netprofit
)
RETURN
Result
Apply it to the table visual
Final output:
Best Regards,
Jianbo Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thnak you very much
@eliasayy , Try a day behind sales using date table
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
or
day Behind =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today()) -1
var _min = if(isfiltered('Date'),Min( 'Date'[Date]) , today()) -1
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))
User | Count |
---|---|
54 | |
48 | |
19 | |
16 | |
15 |
User | Count |
---|---|
115 | |
42 | |
41 | |
26 | |
22 |