How to calculate MTD,last 7 days, Yesterday, today (date range)

[Anonymous]

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

[v-frfei-msft · Accepted 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
        )
    )

[Anonymous]

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