running total for a calculated measure on dates

[BItoken]

Hi All,

I have a measure based on a Sales Date and a selected Calendar date as the selector as below

Booked Early =

var qr = SELECTEDVALUE(QTR[Qtr Name])

var wn = SELECTEDVALUE(Qtr[Week Num])

return

CALCULATE(Sum(SalesTable[Sales Amt]),

Table[sales_fq] = qr,

Table[sales_week] = wn,

)

Output: As in Image Booked Early

Now i need to create a measure for Running total by the week. I am aware of this formula for running total and i used 

SUMX(FILTER(ALLSELECTED(SalesTable[sales_week]), SalesTable[sales_week] <= max(SalesTable[Sales_week])),[Booked Early])

Can anyone help!!

[v-lid-msft]

Hi @BItoken ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[Ashish_Mathur]

Hi,

Ideally, this is what you should be doing:

1. Create a Calendar Table and write calculated column formulas to extract Financial Year, Month and Quarter
2. Build a relationship from the Date column of your base data table to the Date column of your Calendar Table
3. To your visual(s), drag all time dimensions from the Calendar Table
4. Write these measures

Booked Early = Sum(SalesTable[Sales Amt])

Booked early running total = CALCULATE([Booked Early],DATESBETWEEN(Calendar[Date],MINX(ALL(Calendar),Calendar[Date]),MAX(Calendar[Date])))

Hope this helps.

[v-lid-msft]

Hi @BItoken ,

We can try to create a measure to meet your requirement:

Running Booked Early =
CALCULATE (
    SUM ( SalesTable[Sales Amt] ),
    FILTER (
        ALLSELECTED ( SalesTable ),
        SalesTable[sales_fq] <= MinQTR[Qtr Name]
            && SalesTable[sales_week] <= MIN ( Qtr[Week Num] )
    )
)

If it doesn't meet your requirement, Could you please provide a mockup sample based on fake data or describle the fields of each tables and the relations between tables simply?

Please don't contain any Confidential Information or Real data in your reply.

Best regards,