Forum Discussion
TRUE FLASE between two dates
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.
Thanks!
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.
5 Replies
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/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.
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.
