how to show last 20 dates once selectedi n the slicer an specific date?

[RuthMerchán]

Hi everyone!

I would like to know some idea to get this functionality on PowerBI. The goal is once selected an specific date on the slicer (for example April 30, 2023) see in a table last 20 days (each date), I mean in this case from April 10 to April 30. I have a dimension table of dates (dim_tiempo) and a fact table (fact_recogidas) connected by column Fecha and Fecharecogidas, respectively. 

The slicer contains the date of the dimension table and the objective is when a select an specific date in this slicer, it is drawn in a table 20 rows with the last 20 dates until the selected date with the amount of reception of product (two columns, 20 rows). 

I have tried with this formula to create another table with this last 20 days, but it is in blank, it does not work...

JustDate = DATE(YEAR('FACT_RECOGIDA'[FECHA_RECOGIDA]), MONTH('FACT_RECOGIDA'[FECHA_RECOGIDA]), DAY('FACT_RECOGIDA'[FECHA_RECOGIDA]))

Table_last20days =

VAR FechaSeleccionada = SELECTEDVALUE('DIM_TIEMPO'[Fecha])

VAR FechaInicio = FechaSeleccionada - 20

RETURN

ADDCOLUMNS(

   FILTER(

      VALUES('FACT_RECOGIDA'[FechaRecogidaSoloFecha]),

      'FACT_RECOGIDA'[FechaRecogidaSoloFecha] >= FechaInicio && 'FACT_RECOGIDA'[FechaRecogidaSoloFecha] <= FechaSeleccionada

   ),

   "Fecha", 'FACT_RECOGIDA'[FechaRecogidaSoloFecha],

   "Litros", CALCULATE(SUM('FACT_RECOGIDA'[Litros]), 'FACT_RECOGIDA'[FechaRecogidaSoloFecha] = 'FACT_RECOGIDA'[FechaRecogidaSoloFecha])

)

Please, could you help me with it.

Thank you in advance for all your support.

Ruth

[amitchandak · Accepted solution]

@RuthMerchán , in case you just want to display a total of 20 days. You should use date table and time intelligence

Rolling 20 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX('Date'[Date]),-20,DAY))

But if want display date too

then the slicer needs to be on an independent table

//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = _max -20
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))

Need of an Independent Table in Power BI - Exclude: https://youtu.be/lOEW-YUrAbE

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[RuthMerchán]

Hi!

If I have understood well, I have to take year, month and day for slicers from date1, right?

Make a relation of this table with fact_table with column date. And I use Date1[Date] and new measure on my table to show for example data with date from April 1 to April 20 if I select April 20,2023 on slicers, right? 

I have done this but the result is like this:

 Instead of this:

Please, could you tell me if I am right or maybe I have done something wrong...

Thank you so much for your support!

[RuthMerchán]

Hi! 

I have understood what you mean! 

I have taken year, month and day for slicers from date1.

Date1 is an independent table so I have not made relation between this table and others. And I use Date[Date] and new measure on my table to show for example data with date from April 1 to April 20 if I select April 20,2023 on slicers.

It works!

Thank you very much 🙂