dynamic 30/60/90 days filter from selecteddate(not from current date)

[01UmaManoharan]

Hi All,

I have a table with 3 columns ticketnumber, issueddate, cancelleddate. if the cancelleddate column is blank which means the ticket is not cancelled and inprogress. i need to show the cancellations. I have

totalticktes measure which is count(ticketnumber),

cacelledtickets which is count(ticketnumber) with filter NOT ISBLANK (cancelleddate ),

%Cancellation= Divide(totalticktes , cacelledtickets ) (not an actual measure. just writing as a statement here for reference)

i need to show the %cancellation with 15 days/30 days.45.60/90/180/360/2y filters. Basically i'm trying to show the %cancellation after 15 days of the issueddate after 30days from the issueddate and so on.

I'm getting a suggestion to write bunch and bunch of codes for all the days filters. its not just %cancellation, i have other categories %paid, %unpaid that comes from different tables that can deal with.

is there anyway to put this filters in date table itself as a calculated column. i tried datediff between issueddate, cancelleddate as one columns as days count and another calculated column with switch if the days count is 1-15 give 15days performance, 16-30 give 30 days performance, blank as inprogress and so on. but this logic does not work if pull this calculated column in a slicer. am i misssing some logic or is there any better way to do this. Please help me.

Many Thanks,

Uma

[01UmaManoharan]

Thank you for the response. I'm able to understand the logic will try this and check. I have a seperate date table marked as a date table. The date column has been connected with issueddate in my mainticket table.it has 1 to many relationship to maintable

[Anonymous]

Hi @01UmaManoharan ,

Please try below steps:

1. Create a Date Table (if you haven't already): Ensure you have a Date table in your model that includes all dates relevant to your data. This table should be marked as a Date table in Power BI. This is crucial for time intelligence calculations and for creating a dynamic time slicer.

2. Dynamic Time Slicing using DAX: Instead of using calculated columns for each time interval, consider creating a single measure that dynamically calculates the %cancellation based on the selected time interval from a slicer. You can achieve this by creating a slicer based on a disconnected table that contains your desired time intervals (15 days, 30 days, etc.) and then using a DAX measure to calculate %cancellation based on the selected interval. This approach offers greater flexibility and interactivity.

Here's a simplified example of how you might set up the DAX measure:

DynamicCancellationRate = 
    VAR SelectedInterval = SELECTEDVALUE(IntervalTable[Days])
    RETURN
    CALCULATE(
        [YourCancellationRateMeasure],
        FILTER(
            All(TicketsTable),
            DATEDIFF(TicketsTable[issueddate], TicketsTable[cancelleddate], DAY) <= SelectedInterval
            && ISBLANK(TicketsTable[cancelleddate]) = FALSE
        )
    )

This measure calculates the cancellation rate based on the interval selected in the slicer. Note: You'll need to adjust the measure to fit your exact model and requirements.

3. Creating a Disconnected Table for Time Intervals: Use Power Query or DAX to create a table that contains your desired time intervals (e.g., 15, 30, 45 days, etc.). This table will be used for your slicer and won't be directly related to your other tables.

4. Use the Slicer to Filter Reports Dynamically: Once you have your dynamic measure and disconnected table set up, you can add the time interval slicer to your report. Users can select the desired time interval from the slicer to dynamically update the cancellation rate and other metrics.

Best regards,
Community Support Team_Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[01UmaManoharan]

  PeriodTable = DATATABLE("Period", INTEGER, { {15}, {30}, {45}, {60}, {90}, {180}, {360}, {720} })

if i pull this Period column in slicer how it will slice my measure if there is no connectivity with my main table? if i create a calculated column in my main table using another calculated column thats datefiff between issueddate and cancelled date which will give me blanks,0,1,2,3,4,5.... from this i can create a range 1 t0 15 as 15 , 16-30 as 30.. this new column can be connected with period column so that it will have 1:N relations ship. but if i pull 15 in slicer it just picks all 15s and returns 100% as cancellation.

[AmiraBedh]

For dynamic filtering based on periods like 15 days, 30 days, etc., after the `issueddate`, instead of relying on calculated columns, use measures combined with a disconnected date table or parameter table that allows users to select the period for analysis.

 You can create a disconnected table either manually in Power BI or through DAX with fixed values representing your periods (15, 30, 45, 60, 90, 180, 360, 720 days...) :

   PeriodTable = DATATABLE("Period", INTEGER, { {15}, {30}, {45}, {60}, {90}, {180}, {360}, {720} })

Then use a measure to calculate the cancellation rate based on the selected period from `PeriodTable` :

%CancellationDynamic = 
   VAR SelectedPeriod = SELECTEDVALUE(PeriodTable[Period], 0) -- default to 0 if nothing is selected
   RETURN
   CALCULATE(
       DIVIDE(
           COUNTROWS(FILTER('Table', 'Table'[DaysSinceIssued] <= SelectedPeriod AND NOT ISBLANK('Table'[cancelleddate]))),
           COUNTROWS(FILTER('Table', 'Table'[DaysSinceIssued] <= SelectedPeriod)),
           0
       )
   )