10 last days in Date Table

[Chema_Ortega_Ga]

Hello everybody,

I hope you all are very good!!!

I have a Table with dates, and I want to filter only the last ten days... So I need a filter in my report that shows "Last ten days". What I was doing is a new column in the table with the next formula:

Loaded Date 5 = IF(

                    (MAX('NexThing Files'[dataDate]) in {'NexThing Files'[dataDate],'NexThing Files'[dataDate]-1,'NexThing Files'[dataDate]-2,'NexThing Files'[dataDate]-3,'NexThing Files'[dataDate]-4,'NexThing Files'[dataDate]-5,'NexThing Files'[dataDate]-6,'NexThing Files'[dataDate]-7,'NexThing Files'[dataDate]-8,'NexThing Files'[dataDate]-9,'NexThing Files'[dataDate]-10}),

                    "Last 10 Days",

                    format('NexThing Files'[Loaded Date],"dd/mm/yyyy")

                    )

But it does only function properly for the first date:

The formula should put "Last 10 days" in all the yellow dates (10 dates), and in the other dates it should put the date.

Any workaround for this?

Thanks a lot. Best regards,

Chema Ortega

[gvlado]

Try to use filter for this visual or page or all pages on filter pane

[danextian · Accepted solution]

Hi @Chema_Ortega_Ga 

A simple formula as below should work

Date Category = 
IF (
    'NexThing Files'[Loaded Date]
        >= MAX ( 'NexThing Files'[Loaded Date] ) - 9,
    "Last 10 days",
    FORMAT ( 'NexThing Files'[Loaded Date], "dd/mm/yyyy" )
)

Note. if you intend to use the calculated column in a slicer, the values will be sorted alphabetically so the dates will ot be in the correct order. Add another calc column to sort it by.

Date Category Sort = 
IF (
    'NexThing Files'[Loaded Date]
        >= MAX ( 'NexThing Files'[Loaded Date] ) - 9,
    1,
    'NexThing Files'[Loaded Date]
)

Date Category Sort = 
IF (
    'NexThing Files'[Loaded Date]
        >= MAX ( 'NexThing Files'[Loaded Date] ) - 9,
    100000,
    'NexThing Files'[Loaded Date]
)

[Chema_Ortega_Ga]

Hi Danextian... Thanks a lot! Thats what I needed! That is the solution... that's GREAT! Best regards

[Angith_Nair]

Hi @Chema_Ortega_Ga 

Use the below dax:

Loaded Date 5 = 
IF(
    'NexThing Files'[dataDate] >= (MAX('NexThing Files'[dataDate]) - 9) 
        && 'NexThing Files'[dataDate] <= MAX('NexThing Files'[dataDate]),
    "Last 10 Days",
    FORMAT('NexThing Files'[dataDate], "dd/mm/yyyy")
)

[Chema_Ortega_Ga]

Hi Angith,

Thanks a lot! That also works great! Thanks a lot. Best regard!