Slicer to filter dates from 6AM to 6AM

[VD84]

Hi guys,

I am kind of new to PBI, this is mindboggling... I have tried numerous solutions but none seem to work.

I have a slicer that will slice between two dates to filter data on a table and graphs. The difficulty I am having is the data needs to be filtered to show only records from 6AM on the selected From date and UP TO 6AM on the selected To date.

I cannot use a calculated column as when it is calculated the model does not know what the selected from and to dates will be... The problem is there can be two conditions for every date&time, if it is selected as a From date it can only show records AFTER 6AM of the selected from date. But if it is selected as a TO, then it can only show records up until the selected date but BEFORE 6AM.

I have tried using a flag measure/col(that I can filter out) that can perhaps show a 1 if it is outside the "Correct" time range, but I can't get it to work... I tried two different methods, as seen below...

6To6FlagCol =

    var FromCondition = if ('PowerBI_WB_UpdateTransactionReport'[LOGDATE] >= DATE(YEAR(MIN('PowerBI_WB_UpdateTransactionReport'[LOGDATE])),Month(MIN('PowerBI_WB_UpdateTransactionReport'[LOGDATE])),Day(MIN('PowerBI_WB_UpdateTransactionReport'[LOGDATE]))) + Time(6,0,0),1,0)

    --var ToCondition = if('PowerBI_WB_UpdateTransactionReport'[LOGDATE] <= max('PowerBI_WB_UpdateTransactionReport'[LOGDATE]) && 'PowerBI_WB_UpdateTransactionReport'[Time] < Time(6,0,0),1,0)

return

    FromCondition-- + ToCondition

Does anyone have any sugggestions as to how I can tackle this? 

 What I am trying to achieve with the flag:

[DataInsights · Accepted solution]

@VD84,

Try this measure. Calculated columns don't recognize user filters (slicers) so it needs to be a measure. In this example, the date table Dates has no relationship with the fact table. The slicer uses Dates[Date].

6To6Flag = 
VAR vStartDate =
    CALCULATE ( MIN ( Dates[Date] ), ALLSELECTED ( Dates ) )
VAR vEndDate =
    CALCULATE ( MAX ( Dates[Date] ), ALLSELECTED ( Dates ) )
VAR vCurrentLogDatetime =
    MAX ( PowerBI_WB_UpdateTransactionReport[LOGDATE] )
VAR vCurrentLogDate =
    INT ( vCurrentLogDatetime )
VAR vCurrentLogTime =
    TIME ( HOUR ( vCurrentLogDatetime ), MINUTE ( vCurrentLogDatetime ), SECOND ( vCurrentLogDatetime ) )
VAR vResult =
    SWITCH (
        TRUE,
        vCurrentLogDate = vStartDate, IF ( vCurrentLogTime > TIME ( 6, 0, 0 ), 1, 0 ),
        vCurrentLogDate = vEndDate, IF ( vCurrentLogTime < TIME ( 6, 0, 0 ), 1, 0 ),
        vCurrentLogDate > vStartDate && vCurrentLogDate < vEndDate, 1,
        0
    )
RETURN
    vResult

[VD84]

Thanks so much @DataInsights 

This works perfectly, I am so grateful I had many sleepless nights over this, so grateful!

[DataInsights · Accepted solution]

@VD84,

Try this measure. Calculated columns don't recognize user filters (slicers) so it needs to be a measure. In this example, the date table Dates has no relationship with the fact table. The slicer uses Dates[Date].

6To6Flag = 
VAR vStartDate =
    CALCULATE ( MIN ( Dates[Date] ), ALLSELECTED ( Dates ) )
VAR vEndDate =
    CALCULATE ( MAX ( Dates[Date] ), ALLSELECTED ( Dates ) )
VAR vCurrentLogDatetime =
    MAX ( PowerBI_WB_UpdateTransactionReport[LOGDATE] )
VAR vCurrentLogDate =
    INT ( vCurrentLogDatetime )
VAR vCurrentLogTime =
    TIME ( HOUR ( vCurrentLogDatetime ), MINUTE ( vCurrentLogDatetime ), SECOND ( vCurrentLogDatetime ) )
VAR vResult =
    SWITCH (
        TRUE,
        vCurrentLogDate = vStartDate, IF ( vCurrentLogTime > TIME ( 6, 0, 0 ), 1, 0 ),
        vCurrentLogDate = vEndDate, IF ( vCurrentLogTime < TIME ( 6, 0, 0 ), 1, 0 ),
        vCurrentLogDate > vStartDate && vCurrentLogDate < vEndDate, 1,
        0
    )
RETURN
    vResult

[TejasShah]

Hello @DataInsights ,

I had the similar issue, I have used your suggested measure for 6to6Flag but it only works for the From selection, while in the to selection it takes all the after 6am as well

    

Can you please support if possible ?

[TejasShah]

Hello @DataInsights , can you please support on this issue please?

Thanking you in anticipation

[VD84]

Hi @DataInsights where this flag works wonderfully for my table visual, it does not work when I add it as a filter on my other visuals. For example I have a card counting the total number of records

This is just a normal measure like below

Nr of Trx = Count(Transaction_ID)

 , when I try to add the 6To6Flag functionality to this measure as an example:

Nr of Trx = calculate(count(TransactionID),6To6Flag = 1)

I can't add the measure name there...
I think the same problem is causing the graph which also uses Nr of Trx to not show the "6To6Flag" filtered transactions even if I add the visual level filter on them...
Please  can you assist with this?

[DataInsights · Accepted solution]

@VD84,

A calculation group provides a more robust way to create visual filters. Here's the DAX for the calculation item 6To6Flag:

6To6Flag = 
VAR vStartDate =
    CALCULATE ( MIN ( Dates[Date] ), ALLSELECTED ( Dates ) )
VAR vEndDate =
    CALCULATE ( MAX ( Dates[Date] ), ALLSELECTED ( Dates ) )
VAR vRowsInRange =
    FILTER (
        PowerBI_WB_UpdateTransactionReport,
        VAR vCurrentLogDatetime = PowerBI_WB_UpdateTransactionReport[LOGDATE]
        VAR vCurrentLogDate =
            INT ( vCurrentLogDatetime )
        VAR vCurrentLogTime =
            TIME ( HOUR ( vCurrentLogDatetime ), MINUTE ( vCurrentLogDatetime ), SECOND ( vCurrentLogDatetime ) )
        RETURN
            SWITCH (
                TRUE,
                vCurrentLogDate = vStartDate, vCurrentLogTime > TIME ( 6, 0, 0 ),
                vCurrentLogDate = vEndDate, vCurrentLogTime < TIME ( 6, 0, 0 ),
                vCurrentLogDate > vStartDate && vCurrentLogDate < vEndDate
            )
    )
VAR vTransactionIDInRange =
    SELECTCOLUMNS (
        vRowsInRange,
        "Transaction_ID", PowerBI_WB_UpdateTransactionReport[Transaction_ID]
    )
VAR vResult =
    CALCULATE ( SELECTEDMEASURE (), vTransactionIDInRange )
RETURN
    vResult

If your visual doesn't have a measure (required by calculation groups), create a simple row count measure like the one below and add it as a visual filter (greater than 0):

Row Count = COUNTROWS ( PowerBI_WB_UpdateTransactionReport )

To create a calculation group, see the article below by @DataZoe:

https://powerbi.microsoft.com/en-us/blog/deep-dive-into-the-model-explorer-with-calculation-group-au... 

[VD84]

Awesome @DataInsights , this was a learning curve, and I got it to work on my model thanks so much, I really appreciate your help again 🙂

[DataInsights]

Glad this solution worked. Calculation groups take a little time to learn, but the payoff is huge.