Get and use slicer value

[Anonymous]

Hi Community,

As a starter with Power BI, i am trying to create a new table based on another query, which is filtered with a "between dates" slicer. When generating the new table i am not able to get the MIN date with the filters applied.

For so far i have used the following working steps:

1. Created a new table with a set of dates from 2010 till today

localDates = 
ADDCOLUMNS (
CALENDAR (DATE(2010;1;1); TODAY());
"Format"; FORMAT([Date]; "ddmmyyyy");
"DatumNormalized"; VALUE(FORMAT([Date];"yyyy"))
)

2. Added a slicer and a table to filter and view the dates

The next thing i am trying to do, is creating a new table based on the filter set for the table "localDates".

I am trying to use the following DAX to achieve this:

localFilteredRevenue = 
VAR minDate = CALCULATE(MIN(localDates[Date]); ALLSELECTED(localDates))
RETURN ADDCOLUMNS(FILTER(cvGlobalRevenue; cvGlobalRevenue[DatumInvoer] > minDate); "CalculatedMinDate"; minDate)

The result of this is that it is creating a new table but this one is not applying the minDate value from the localDates(filtered) table correctly, it is not even calculating the filter, but it shows the smallest value of the other source date:

I hope someone can help me out with this.

Thanks!

JG

[v-jiascu-msft]

Hi JG,

As we can see from the snapshots and the formula, you did create a calculated table. I'm afraid we can't do it like that. Because the calculated table can't respond to the filters or slicers in the report view. 

What's your goal?

Best Regards,

[Anonymous]

Hi v-jiascu-msft,

Thanks for your reply!

Allright, i am sorry to hear that. My goal is to create multiple datasets, selected on a previous period in time based on the selection made in the slicer. 

When for exmaple the slicer selection is equal to the start and end of the current week, month or year, i would like to create datasets with the 3 previous time periods also. I would like to use this to show revenue in equal previous periods, but in different graphs.

An ideal situation:

localPreviousRevenue =
VAR periodLength = DATEDIFF(localDates[MinDate];localDates[MaxDate];DAY)
VAR minDatePrevious = DATEADD(localDates[MinDate]; (periodLength * -1); DAY)
VAR maxDatePrevious = DATEADD(minDatePrevious; periodLength; DAY)
RETURN FILTER(
    cvGlobalRevenue; 
    cvGlobalRevenue[Date] > minDatePrevious; 
    cvGlobalRevenue[Date] < maxDatePrevious
    )

Best Regards,

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

Usually, we only need one date table in Power BI. We can make the periods dynamic using the filters, slicers, and measures. 

Can you share a dummy sample and the expected result? Maybe the community can give you a solution.

Best Regards,