Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello
I would like to to calculate date range and add filter date to show data in range of dates.
to make it more clear, I want to add filter (slicer) as checklet that has the types below
ID -----|---- TYPE
1-------|----Today
2-------|----Yesterday
3-------|----Last 7 Days
4-------|----MTD
let's assume that there is a simple table with date column "select ITEMID, Factory,CretedDT FROM Xtable" what I have to do is making relationship between the slicer that I added before and this Xtable
I tried to add new coulmn in SQL server using case statement as showing below
select
ITEMID,
Factory,
case
when [CretedDT ] = CONVERT(DATE,GETDATE()) then 1
when [CretedDT ] = dateadd(day,-1, cast(getdate() as date))then 2
when [CretedDT ] >= DATEADD( DAY, DATEDIFF(DAY, 0, GETDATE()) - 7, 0) then 3
end as Filter_Date
FROM Xtable
but this method has a problem, it would mean I cant include neither today (1) type nor yesterday (2) type into Last 7 days type (3), it is always be only one type, therefore the data wont be accurate, what if the user choose type number 3 the data of yesterday and tody wont appear.
is there any way to simplify what I need and do it directly from Power BI by using measures or adding conditional coulmn?
NOTE: The data connectivity mode is DirectQuery
Solved! Go to Solution.
Hi @Anonymous ,
To create a measure as below.
Measure = VAR tod = TODAY () VAR yd = YEAR ( TODAY () ) VAR md = MONTH ( TODAY () ) VAR yest = tod - 1 VAR last7 = tod - 7 VAR sele = SELECTEDVALUE ( Slicer[TYPE] ) VAR todaysales = CALCULATE ( SUM ( 'data'[value] ), FILTER ( 'data', 'data'[date] = tod ) ) VAR yestsales = CALCULATE ( SUM ( 'data'[value] ), FILTER ( 'data', 'data'[date] = yest ) ) VAR last7sales = CALCULATE ( SUM ( 'data'[value] ), FILTER ( 'data', 'data'[date] <= tod && 'data'[date] > last7 ) ) VAR mtdsale = CALCULATE ( SUM ( 'data'[value] ), FILTER ( 'data', YEAR ( 'data'[date] ) = yd && MONTH ( 'data'[date] ) = md && 'data'[date] <= tod ) ) RETURN IF ( ISFILTERED ( Slicer[TYPE] ), SWITCH ( TRUE (), sele = "Today", todaysales, sele = "Yesterday", yestsales, sele = "Last 7 days", last7sales, sele = "MTD", mtdsale ) )
Hi @Anonymous ,
To create a measure as below.
Measure = VAR tod = TODAY () VAR yd = YEAR ( TODAY () ) VAR md = MONTH ( TODAY () ) VAR yest = tod - 1 VAR last7 = tod - 7 VAR sele = SELECTEDVALUE ( Slicer[TYPE] ) VAR todaysales = CALCULATE ( SUM ( 'data'[value] ), FILTER ( 'data', 'data'[date] = tod ) ) VAR yestsales = CALCULATE ( SUM ( 'data'[value] ), FILTER ( 'data', 'data'[date] = yest ) ) VAR last7sales = CALCULATE ( SUM ( 'data'[value] ), FILTER ( 'data', 'data'[date] <= tod && 'data'[date] > last7 ) ) VAR mtdsale = CALCULATE ( SUM ( 'data'[value] ), FILTER ( 'data', YEAR ( 'data'[date] ) = yd && MONTH ( 'data'[date] ) = md && 'data'[date] <= tod ) ) RETURN IF ( ISFILTERED ( Slicer[TYPE] ), SWITCH ( TRUE (), sele = "Today", todaysales, sele = "Yesterday", yestsales, sele = "Last 7 days", last7sales, sele = "MTD", mtdsale ) )
Hello
I would like to to calculate date range and add filter date to show data in range of dates.
to make it more clear, I want to add filter (slicer) as checklet that has the types below
ID -----|---- TYPE
1-------|----Today
2-------|----Yesterday
3-------|----Last 7 Days
4-------|----MTD
let's assume that there is a simple table with date column "select ITEMID, Factory,CretedDT FROM Xtable" what I have to do is making relationship between the slicer that I added before and this Xtable
I tried to add new coulmn in SQL server using case statement as showing below
select
ITEMID,
Factory,
case
when [CretedDT ] = CONVERT(DATE,GETDATE()) then 1
when [CretedDT ] = dateadd(day,-1, cast(getdate() as date))then 2
when [CretedDT ] >= DATEADD( DAY, DATEDIFF(DAY, 0, GETDATE()) - 7, 0) then 3
end as Filter_Date
FROM Xtable
but this method has a problem, it would mean I cant include neither today (1) type nor yesterday (2) type into Last 7 days type (3), it is always be only one type, therefore the data wont be accurate, what if the user choose type number 3 the data of yesterday and tody wont appear.
is there any way to simplify what I need and do it directly from Power BI by using measures or adding conditional coulmn?
NOTE: The data connectivity mode is DirectQuery
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.