Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
METALES
Frequent Visitor

I need help to create 2 columns to check the latest records without repeating

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:

DatePoliceValue 1Value 2Value 2COLUMN LAST DATECOLUMN LAST 15 DAYS OLD DATE
18/4/21 14:351123TRUEFALSE
18/4/21 14:341132FALSEFALSE
18/4/21 14:352321TRUETRUE
16/4/21 13:003333TRUETRUE
15/4/21 13:234222TRUETRUE
14/9/21 4:001111FALSEFALSE
03/04/2021 10:001058FALSETRUE


Any help would be gladly!

2 ACCEPTED SOLUTIONS
v-yiruan-msft
Community Support
Community Support

Hi @METALES ,

I created a sample pbix file(see attachment), please check whether that is what you want.

yingyinr_1-1618901399812.png

If the above one is not what you want, please provide your expected result with sample and calculation logic. Thank you.

Best Regards

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

View solution in original post

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. 

View solution in original post

8 REPLIES 8
v-yiruan-msft
Community Support
Community Support

Hi @METALES ,

I created a sample pbix file(see attachment), please check whether that is what you want.

yingyinr_1-1618901399812.png

If the above one is not what you want, please provide your expected result with sample and calculation logic. Thank you.

Best Regards

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

Hi @v-yiruan-msft.
I tested it and if you add more data 15 days ago it still returns all dates as TRUE.

METALES_0-1619019312168.png

 

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

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

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.
METALES_1-1619099411224.png

 

Would there be any way to consider the same criteria as the current day for 15 days ago?

METALES_0-1619099170228.png

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.

lbendlin
Super User
Super User

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.