Forum Discussion

E_Rye's avatar
E_Rye
Frequent Visitor
7 months ago
Solved

Running Monthly Total Calculation or Filter

Good day, Will someone please help me understand the steps to create a running monthly total that can be filtered by dates/conditions?    All my data is in one table (Episodes) with one row per un...
General Comment
Need Help
Tutorial Requests
  • krishnakanth240's avatar
    krishnakanth240
    7 months ago

    Hi E_Rye 

     

    Create a Date table( please check this post - https://community.fabric.microsoft.com/t5/Desktop/Creating-Date-Tables/m-p/553980) then provide relationship based on date column from Date table to your Main table

     

    Create a measure
    Active IDs =
    CALCULATE(COUNTROWS(Episodes),FILTER(Episodes,Episodes[Episode Start Date <=MAX(Date[MonthEnd] )&&(
    ISBLANK( Episodes[Delivery Date])
    || Episodes[Delivery Date]>=MIN(Date[MonthStart]))))

     

    Cumulative Active IDs =CALCULATE([Active IDs],
    FILTER(ALL(Date[Date]),Date[Date]<=MAX( Date[Date])))

FBergamaschi's avatar
FBergamaschi
Super User
7 months ago

Hi krishnakanth240,

the general pattern is something link this

 

Active IDs =
VAR MaxDate = MAX(Date[MonthEnd] )
VAR MinDate = MIN ( Date[MonthEnd] )
RETURN
CALCULATE(
           COUNTROWS(Episodes),

           ( Episodes[Episode Start Date <=MaxDate && ISBLANK( Episodes[Delivery Date]) ) ||
           Episodes[Delivery Date]>=MinDate,
          REMOVEFILTERS ( Episodes )
)

 

Cumul Active IDs =
VAR MaxDate = MAX(Date[MonthEnd] )
RETURN
CALCULATE (
             [Active IDs],
             Date[Date]<=MaxDate
)

 

But to be sure to provide the righe answer, I need to know how you arrange the matrix (what you have in rows, columns, etc)

 

Best

If this helped, please consider giving kudos and mark as a solution

@me in replies or I'll lose your thread

Want to check your DAX skills? Answer my biweekly DAX challenges on the kubisco Linkedin page

Consider voting this Power BI idea

Francesco Bergamaschi

MBA, M.Eng, M.Econ, Professor of BI