TRUE FLASE between two dates

[xonder]

I collect data of advertised vehicles at irregulars intervals. Each time I collect data, I get a snapshot of what is currently being advertised along with a time stamp.

05.11.2020 is the first date of my data set and 08.11.2020 is the last date of my data set

ID	05.11.2020	06.11.2020	07.11.2020	08.11.2020
123456	1	1	1	1
789011	1	1	1	0
121314	0	1	1	1
151617	0	1	1	0

I would like to  be able to answer the question "How many vehicles have been sold between dd.mm.yyyy and DD.MM.YYYY?"

A vehicle is sold if last date of a given ID is smaller than the last date of my data set.

If dd.mm.yyyy = 05.11.2020 and DD.MM.YYYY = 08.11.2020 then 2 vehicles, 789011 and 151617 have been sold.

If I change my page filter so that dd.mm.yyyy = 05.11.2020. and DD.MM.YYYY = 07.11.2020 then 0 vehicles have been sold.

How can I create a TRUE/FLASE measure that tells me wheather an ID has been sald and make sure that this measure changes depending on the date range selected.

here is by pbix file 

Thanks!

[v-janeyg-msft · Accepted solution]

Hi, @xonder 

It’s my pleasure to answer for you.

According to your description,I think you can create a date slicer,then create a measure to calculate the desired rusult.

Like this:

Measure =
VAR a =
    SUMMARIZE (
        ALL ( azw ),
        [ID],
        "lastdata", MAX ( azw[date] ),
        "firstdata", MIN ( azw[date] )
    )
VAR selectedmin =
    CALCULATE ( MIN ( azw[date] ), ALLSELECTED ( azw ) )
VAR selectedmax =
    CALCULATE ( MAX ( azw[date] ), ALLSELECTED ( azw ) )
VAR b =
    ADDCOLUMNS (
        a,
        "status",
            IF (
                NOT ( [lastdata] < selectedmin
                    || [firstdata] > selectedmax ),
                IF ( [lastdata] < selectedmax, 1, 0 )
            )
    )
RETURN
    SUMX ( b, [status] )

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-janeyg-msft · Accepted solution]

Hi, @xonder 

It’s my pleasure to answer for you.

According to your description,I think you can create a date slicer,then create a measure to calculate the desired rusult.

Like this:

Measure =
VAR a =
    SUMMARIZE (
        ALL ( azw ),
        [ID],
        "lastdata", MAX ( azw[date] ),
        "firstdata", MIN ( azw[date] )
    )
VAR selectedmin =
    CALCULATE ( MIN ( azw[date] ), ALLSELECTED ( azw ) )
VAR selectedmax =
    CALCULATE ( MAX ( azw[date] ), ALLSELECTED ( azw ) )
VAR b =
    ADDCOLUMNS (
        a,
        "status",
            IF (
                NOT ( [lastdata] < selectedmin
                    || [firstdata] > selectedmax ),
                IF ( [lastdata] < selectedmax, 1, 0 )
            )
    )
RETURN
    SUMX ( b, [status] )

Here is my sample .pbix file.Hope it helps.

If it doesn’t solve your problem, please feel free to ask me.

Best Regards

Janey Guo

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[xonder]

Hi @v-janeyg-msft ,

Is there a way to make the measure work with other filters than than azw[date]. All the charts on my work when filtered by azw[brand] except this one.

Here is an example of filtering by azw[brand] = "Mercedes". The number of cars sold (106) shoudl theoretically drop when this is selected to show the number of Mercedes' sold.

[v-janeyg-msft · Accepted solution]

Hi， @xonder 

Try to change 'all' to 'allselected' in the above formula.

Best Regards

Janey Guo

[xonder]

Hi @v-janeyg-msft ,

I get inconsitent results depending on the filter I use.

I created the measure as per you instructions:

Cars Sold = 
VAR a =
    SUMMARIZE (
        ALLSELECTED( azw ),
        [ID],
        "lastdata", MAX ( azw[date] ),
        "firstdata", MIN ( azw[date] )
    )
VAR selectedmin =
    CALCULATE ( MIN ( azw[date] ), ALLSELECTED ( azw ) )
VAR selectedmax =
    CALCULATE ( MAX ( azw[date] ), ALLSELECTED ( azw ) )
VAR b =
    ADDCOLUMNS (
        a,
        "status",
            IF (
                NOT ( [lastdata] < selectedmin
                    || [firstdata] > selectedmax ),
                IF ( [lastdata] < selectedmax, 1, 0 )
            )
    )
RETURN
    SUMX ( b, [status] )

And it works exactly as it should giving the correct result.

I then summarize this in a new table where I am able to qualify each ID at "Sold" or "For Sale":

Sale = 
VAR a =
    SUMMARIZE (
        ALLSELECTED( azw ),
        [ID],
        "lastdata", MAX ( azw[date] ),
        "firstdata", MIN ( azw[date] )
    )
VAR selectedmin =
    CALCULATE ( MIN ( azw[date] ), ALLSELECTED ( azw ) )
VAR selectedmax =
    CALCULATE ( MAX ( azw[date] ), ALLSELECTED ( azw ) )
VAR b =
    ADDCOLUMNS (
        a,
        "status",
            IF (
                NOT ( [lastdata] < selectedmin
                    || [firstdata] > selectedmax ),
                IF ( [lastdata] < selectedmax, "Sold", "For Sale" )
            )
    )
RETURN
    SUMMARIZE(b, azw[ID], [status], [lastdata], "selectedmax", CALCULATE(selectedmax))

And I get this table (hidden column title is "Cars Sold"):

Now I would like to use this table so this I can filter by sold/for sale. By deifintion, if I filter by sold, I should get keep the same total of 192. But for some reason this change to 186 as show below:

Do you have any explanation why the last 6 IDs are correctly marked as "Sold" but incorrection give value of 0?

Many thanks for your help.

[amitchandak]

@xonder , if the table shows your raw data. You have to unpivot that

https://radacad.com/pivot-and-unpivot-with-power-bi
Transpose : https://yodalearning.com/tutorials/power-query-helps-transposing-data/