Measure to filter a table with last date values

[rmeng1975]

https://1drv.ms/u/s!Am7PNkfu1RRdgrp4ixgtjrtJP22N_w?e=86giCO 
The objective is if I don´t filter the date slicer by default the table shows the last day activations (last day = 06/02/2024 and Type ="Active"):

DateTypeNameStateOriginQuantity

07/02/2024	Parq	Anual Campanha	Activo		72
07/02/2024	Parq	+ Anual	Activo		13
07/02/2024	Parq	canal +	Activo		825
07/02/2024	Parq	Anual Campanha	Activo		6
07/02/2024	Parq	+ Anual	Activo		313
07/02/2024	Parq	canal +	Activo		19
06/02/2024	Parq	+ Anual	Activo		308
06/02/2024	Parq	+ Anual	Activo		13
06/02/2024	Parq	Anual Campanha	Activo		6
06/02/2024	Parq	Anual Campanha	Activo		71
06/02/2024	Active	canal +		UFE	3
06/02/2024	Active	canal +		APP/ Web	1
06/02/2024	Active	canal +		Box	7
06/02/2024	Unactive	+ Anual		Outros	1
06/02/2024	Parq	canal +	Activo		819
06/02/2024	Parq	canal +	Activo		19
05/02/2024	Active	canal +		UFE	2
05/02/2024	Active	canal +		APP/ Web	2
05/02/2024	Active	canal +		Box	3
05/02/2024	Active	+ Anual		Box	1
05/02/2024	Unactive	Anual Campanha		Outros	1
05/02/2024	Unactive	canal +		Outros	5
05/02/2024	Parq	canal +	Activo		823
05/02/2024	Parq	canal +	Activo		18
05/02/2024	Parq	+ Anual	Activo		314
05/02/2024	Parq	+ Anual	Activo		13
05/02/2024	Parq	Anual Campanha	Activo		6
05/02/2024	Parq	Anual Campanha	Activo		72
04/02/2024	Parq	canal +	Activo		818
04/02/2024	Active	canal +		APP/ Web	1
04/02/2024	Active	canal +		Box	1
04/02/2024	Unactive	+ Anual		Outros	1
04/02/2024	Unactive	canal +		Outros	2
04/02/2024	Active	+ Anual		APP/ Web

I have created a measure give me the correct total but show all dates   when only 06/02/2024 should appear:

#Last Date Activations = 
VAR _SelectedDate = 
CALCULATE ( MAX ( Canal[Date] ), 'Canal'[Type] = "Active" )
RETURN
    IF (
        ISFILTERED ( 'Date'[Date] ),
        [# Total Activations],
        CALCULATE (
            [# Total Activations],
            Canal,
             Canal[date] = _SelectedDate
        )
    )

The result shoul be a table like this :
DateTypeNameStateOriginQuantity

06/02/2024	Active	canal +		Box	7
06/02/2024	Active	canal +		APP/ Web	1
06/02/2024	Active	canal +		UFE	3

thanks

[talespin · Accepted solution]

hi @rmeng1975 ,

Please try this.

Step1 : Create below measure.

Step2: Set a filter on Table visual as shown in screenshot.

Create this measure

IsFiltered =

VAR _IsFilteredDate = SELECTEDVALUE('Date'[Date])

VAR _IsFilteredCanalDate = SELECTEDVALUE(Canal[Date])

VAR _LastActivatedDate = CALCULATE([# Last Date Activated], REMOVEFILTERS(Canal))

VAR _LastDateActivations = [#Last Date Activations]

RETURN IF(

            ISBLANK(_IsFilteredDate),

            IF(_IsFilteredCanalDate = _LastActivatedDate && NOT(ISBLANK(_LastDateActivations)), 1 , 0 ),

            1

)

[rmeng1975]

It is in the PBIX shared link. But here it is 

# Total Activations =

CALCULATE ( [# Quantity], Canal[Type] = "Active" )

[talespin]

hi @rmeng1975 ,

Please share this measure "[# Ativação]"

[lbendlin]

Measures cannot return tables, they can only return scalar values.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[rmeng1975]

Thanks for the feedback, I know Measures cannot return tables but the purpose is to use the measure inside the table and filter it. Filter bythe last date and is active.