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.
In SQL I can make filters for the last release and for the last 15 days. How would I do this in Power BI without using SQL?
Exemple:
Date | Police | Value 1 | Value 2 | Value 2 | COLUMN LAST DATE | COLUMN LAST 15 DAYS OLD DATE |
18/4/21 14:35 | 1 | 1 | 2 | 3 | TRUE | FALSE |
18/4/21 14:34 | 1 | 1 | 3 | 2 | FALSE | FALSE |
18/4/21 14:35 | 2 | 3 | 2 | 1 | TRUE | TRUE |
16/4/21 13:00 | 3 | 3 | 3 | 3 | TRUE | TRUE |
15/4/21 13:23 | 4 | 2 | 2 | 2 | TRUE | TRUE |
14/9/21 4:00 | 1 | 1 | 1 | 1 | FALSE | FALSE |
03/04/2021 10:00 | 1 | 0 | 5 | 8 | FALSE | TRUE |
Any help would be gladly!
Solved! Go to Solution.
Hi @METALES ,
I created a sample pbix file(see attachment), please check whether that is what you want.
If the above one is not what you want, please provide your expected result with sample and calculation logic. Thank you.
Best Regards
It was not the solution, but I found a way to solve this problem.
Transform Ddate> Separate Date and Hour with space delimiter> select Date and Police> remove duplicates.
Hi @METALES ,
I created a sample pbix file(see attachment), please check whether that is what you want.
If the above one is not what you want, please provide your expected result with sample and calculation logic. Thank you.
Best Regards
Hi @v-yiruan-msft.
I tested it and if you add more data 15 days ago it still returns all dates as TRUE.
Hi @METALES ,
What is the calculation logic of the last two columns? The formula I provided before is to group by police first, then get the maximum date of each police. If the current police has only one record or if there are multiple records and the date corresponding to the police is 15 days before the maximum date, it will display TRUE. Otherwise, FALSE is displayed.
If the above understanding is correct, please update the formula of calculated column [COLUMN LAST 15 DAYS OLD DATE ] as below and check whether you can get the correct result.
COLUMN LAST 15 DAYS OLD DATE =
VAR _count =
CALCULATE ( COUNT ( 'Table'[Date] ), ALLEXCEPT ( 'Table', 'Table'[Police] ) )
RETURN
IF (
_count = 1
|| DATEDIFF ( 'Table'[Date], [LAST DATE], DAY ) >= 15,
"TRUE",
"FALSE"
)
|
Best Regards
Hi @v-yiruan-msft, my English is not the best. =/
But I'll try to explain. Power BI manages to identify that police 1 launched on 21/04/2021 at 13:35 is more recent than police 1 launched on 21/04/2021 13:30 based on the most current records.
Would there be any way to consider the same criteria as the current day for 15 days ago?
Thank you for the patience.
It was not the solution, but I found a way to solve this problem.
Transform Ddate> Separate Date and Hour with space delimiter> select Date and Police> remove duplicates.
Thanks ❤️
I can now remove the "adjustment" in the SQL and use it only in power bi.
Please explain the COLUMN LAST DATE rule for the last two rows.
Its a calculate column, it will always true for the last record and false for the rest.
I would need to check (SUM) every last record from the same instance and 15 days ago.
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.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |