Forum Discussion

hillad92's avatar
hillad92
Frequent Visitor
9 years ago
Solved

Present data between a time range

Hi! I'm trying to add a date slicer to the dashboard, so the dashboard will present data from the last/next three months. I add columns with calculated dates and tried to add them to the filter in ...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    Hi Anonymous,

    Based on my understanding, you can create a calendar table. Create calculated column to get Year, Month,Week.

    Year = YEAR(Test[Date])
Month = MONTH(Test[Date])


    Second, create tree measures using the formulas.

    last/next three month = CALCULATE(MAX(Fact[value]),FILTER(Fact,(Fact[Month]<=MONTH(MAXX(ALL(Test),Test[Date]))+3)&&(Fact[Month]>=MONTH(MAXX(ALL(Test),Test[Date]))-3)))


    Third, create a new table including the values and the[last/next three month] value.

    If this is not what you want, please share your sample table for further analysis.


    Thanks,
    Angelia

Salvador's avatar
Salvador
Responsive Resident
9 years ago

Hi,

 

You could try making a column that reflects the time diff between the date you wanna check, and "now()", and then apply filters that force it to be up to +-90 days. I use this formula: 

Collections Delay =  

    SWITCH ( 

    TRUE (); 

    Now() < Invoices[Overdue Date]; DATEDIFF ( NOW();Invoices[Overdue Date]; DAY )* -1; 

    Now() > Invoices[Overdue Date]; DATEDIFF ( Invoices[Overdue Date]; Now(); DAY ); 

    0)

 

So, if this is between -90 and +90, only data from that period will be shown.