reset slicer to a default from- Rolling 14 Days

[Anonymous]

I have 6 reports on 1 single workbook.  Each chart has a date field the determines the last 14 days (rolling), each refrences the fields pertient to each report.   I am currently still working in the Desktop version.

14DateRangeCt = COUNTX(Filter(AccountProd,AccountProd[Created]>=TODAY()-14),AccountProd[Id])

I also included a date slicer which allows the user to change the date range, however, due to the 14 day measure, it will display 14 day range based on new date.

Is there a way via MQuery/Dax to change the slicer to reset to the 14day on current date(Measure) based on the chart selected or maybe a case statement of some kind?

Any ideas are greatly appreciated.

K

[v-juanli-msft]

Hi @Anonymous 

Is this problem sloved?

If not, please let me know.

Best Regards

Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept  natelpeterson's answer as the solution to help the other members find it more quickly.

[Anonymous]

@Anonymous  - 

You could do something like:

14DateRangeCt =
var selecteddate = SELECTEDVALUE('Date'[Date],TODAY())
return CALCULATE(COUNTROWS(AccountProd),DATESBETWEEN('Date'[Date],selecteddate-14,selecteddate))

Note that I'm using a Date table here. You'll need to substitute 'Date'[Date] with the appropriate date column.

Cheers!

Nathan

[Anonymous]

Thanks for the input, but how do I incorporate all the different date fields used in the different reports?

K

[Anonymous · Accepted solution]

@Anonymous  - By "different reports" do you mean different fact tables within your model?

I'd recommend using a single Date dimension table, and create a relationship between it and each fact table. If you don't have a date dimension table, there are many examples of DAX and M scripts to create one.

If you want to count the number of rows in different tables, you can use the following logic:

Create a Parameters Calculated Table:

Parameters = 
 DATATABLE(
    "TableToQuery", STRING, 
    {{"AccountProd"},{"Table2"},{"Table3"},{"etc"}}
)

Create a Measure to check value of the Parameters table, along with a default.

Selected Table = SELECTEDVALUE('Parameters'[TableToQuery], "AccountProd")

Create your Measure, based off of the choice in Parameters table: 

14DateRangeCt = 
var selecteddate = SELECTEDVALUE('Date'[Date],TODAY())
return 
SWITCH(
    [Selected Table],
    "Table1", CALCULATE(COUNTROWS(Table1),DATESBETWEEN('Date'[Date],selecteddate-14,selecteddate)),
    "Table2", CALCULATE(COUNTROWS(Table2),DATESBETWEEN('Date'[Date],selecteddate-14,selecteddate)),
    CALCULATE(COUNTROWS(AccountProd),DATESBETWEEN('Date'[Date],selecteddate-14,selecteddate))
)

[Anonymous]

This is a good possibility, however, I went a little simpler by using the DimDate Table and reslationships, alos with the Slicer thate allowed for the Last # of days.

Thanks,

For you intput.