Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Chosen period, Running total on Graph Not working

Dear developers,

 

I tried to get a running total day by day , of the current month.

I used the dax methodology  of adding  A <= Max(A).

However it still does not show running total sum.

 

Could any master help?

 

Best regards,

Yuqi

 

Running total not workingRunning total not working

 
 
 
15 REPLIES 15
Anonymous
Not applicable

Hello @Anonymous ,

Please try out below one. I put few comments to explain what is happening in each step.

 

I assume that there is a relationship between DateT and table storing values.

 

 

Measure = 

//Calculate Range to be Aggregated

    //Start off by taking latest date in selection this is also the day when calculation should end
    var vLDate = max(DateT[CalendarDate])
    
    //Get Start Date as 1st day of month - We will use it to aggregate values
    var vStartDate = DATE(Year(vLDate),Month(vLDate),1)

Return // Now to main thing

CALCULATE(
    Sum('OM Import link to P'[Turnover (value/+) (CUR)])
    ,
    All(DateT) // Turn off any filtering on Your calendar table
    ,
    DateT[CalendarDate] >=vStartDate // Fix calculation to sum always from 1st month day
    ,
    DateT[CalendarDate] <=vLDate  // Fix calculation to end sum on last day (selected one in chart)
)

 

 

 

Anonymous
Not applicable

@Anonymous  Thanks for fast Reply!

I tried out your coding, the result is ONLY doing Sum , but not Running sum, however it at least minimize the date period as right range . (From current month start to yesterday)

 
 

I tried also other type of coding, it shows me the running sum result, but not right range.

it basically does not allow me to use the DateT date reference in this calculation(Gray out).

"DateT " table is linked to "Sellin Current Month CY" , using "Calendar Day" which is only text format , eg 20200201. 

 

Best regards,

Yuqi

 

Anonymous
Not applicable

Running Sum1.jpgRunning Sum2.jpg

Anonymous
Not applicable

@Anonymous ,

 

Dont konw why that I am not allowed to insert text and picture at same time, 

So I paste the picture sepearately above. 

Anonymous
Not applicable

Hi @Anonymous ,

 

My piece may not work with SUMX (i am using SUM), but since you found working one than we can try best of both 🙂

 

 

Measure = 

Var vLDate = TODAY()-1
var VMonthText =Value(FORMAT(vLDate,"YYYYMM") &"01") //PArse date to YearMonthText then add "01" and parse it back to number

Return

CALCULATE(
    SUMX('Sellin Current Month CY','Sellin Current Month CY'[Turnover (Value/+) (CUR)]/1000000),
    FILTER(ALLEXCEPT('Sellin Current Month CY','Sellin Current Month CY'[CalendarMonth]),
        And(
        'Selling Current Month CY'[Calendar Day]<=max('Sellin Current Month CY'[Calendar Day])
        ,
        'Selling Current Month CY'[Calendar Day]>=VMonthText)
        )
        )
    )

 

 

Hopefully it will work for you

Anonymous
Not applicable

Hi @Anonymous ,

 

I have changed from Sumx to SUm, it still shows the same..

BTW, tried out your new coding, it went error.

I even tried to map a column with real Date format, the result turned to be a strait line of Total sum.

 

But thanks for tring to help! 

 

Best regards,

Yuqi

Running Sum4.jpgRunning Sum5.jpgRunning Sum3.jpg

Anonymous
Not applicable

Hi @Anonymous 

 

I tested this on PBI with some dummy data (unfortunatelly cannot se an option to send file. Maybe with below details it will work.

 

 

Data Model:

Picture1.png

 

 

 

Results:

Picture1.png

 

Formulas:

M01 = 

//Calculate Range to be Aggregated

    //Start off by taking latest date in selection this is also the day when calculation should end
    var vLDate = Now()
    Var vSDate = max(DateT[Calendar Day (Date)])
    
    //Get Start Date as 1st day of month - We will use it to aggregate values
    var vStartDate = DATE(Year(vLDate),Month(vLDate),1)

Return // Now to main thing

CALCULATE(
    Sum('Sellin Current Month CV'[Turnover (Value/+) (CUR)])
    ,
    All(DateT) // Turn off any filtering on Your calendar table
    ,
    DateT[Calendar Day (Date)] >=vStartDate // Fix calculation to sum always from 1st month day
    ,
    DateT[Calendar Day (Date)] <=vSDate  // Fix calculation to end sum on last day (selected one in chart)
)
/////////////////////////////////////////////////////
M02 = //Calculate Range to be Aggregated

    //Start off by taking latest date in selection this is also the day when calculation should end
    var vLDate = FORMAT(Now(),"YYYYMMDD")
    Var vSDate = max(DateT[Calendar Day]) // Capture which day is selected on chart
    
    //Get Start Date as 1st day of month - We will use it to aggregate values
    var vStartDate = Value((LEFT(vLDate,6)&"01"))

Return // Now to main thing

CALCULATE(
    Sum('Sellin Current Month CV'[Turnover (Value/+) (CUR)])
    ,
    All(DateT) // Turn off any filtering on Your calendar table
    ,
    DateT[Calendar Day] >=vStartDate // Fix calculation to sum always from 1st month day
    ,
    DateT[Calendar Day] <=vSDate  // Fix calculation to end sum on last day (selected one in chart)
)

 

Anonymous
Not applicable

Hi @Anonymous , thanks for feedback.

I made a same data model, as you did. creating a seperate dataTest tabel with only two columns.

 

Modle1.jpgModel 2.jpg

 

Result is still not working, maybe it is due to I have 1 to many relationship? (same day with different turnover records per regions. )

I could neither use the powerbi TotalMTD calculation, even I have linked "Calendar  Day" with " Calendar Day (Date format)"

 

Result.jpg

Anonymous
Not applicable

Hi @Anonymous 

 

In the visual, could you please try using Calendar column from "DateTest" Table?

 

Anonymous
Not applicable

Thanks  @Anonymous  for the quick help, 

 

That helped half way. 

The start date and accumulation is now correct!!!!!! 
Question is , the running sum is not stopping at month End. 
Running Sum1.jpg
 
THe moment i change the last coding line to DateT[Date] <= vEndDate (Last day of Feb),
there is only a straight line left in visual. 
 
Running Sum2.jpg
Anonymous
Not applicable

Hi @Anonymous 

 

There are few ways to fix this:

 

Option 1 : 

You can add additional Fliter to measure just after "DateT[Date]<=vSDate"  add ",DateT[Date]<=vLDate" that will prevent measure to calculating past current date

 

Option 2:

You can modify a bit formula generating DateT Table. In End condition just ust Now(). Then calendar axis will not exceed current date, thus measure will not have anything co calculate

 

Option 3:

You can add relative date filter on visual like (Last 60 days with Today checkbox selected). Metric will still calculate further, but visual will display only last 60 days. Since Measure do not calculate past months it will remove those dimensions showing results for current month only.

 

 

 

Anonymous
Not applicable

Hi @Anonymous ,

 

For option 1, I would assume this is the easiest way, however it does not work. For option 2, It works only if you had one visual. But I need to show full month turnover data from last year same month on the visual at the same time, so this option does not help.  For opthion 3, it does not help for same reason.

 

For option 1 , I had the coding as following

Measure test now =
Var vLDate=NOW()

Var vSdate=Max(DateT[Date])

VAR vStartDate = Date(year(vLDate),MONTH(vLDate),1)

Return

CALCULATE( SUM('Sellin Current Month CY'[Turnover (value/+) (CUR)])/1000000,

ALL(DateT),

DateT[Date] >= vStartDate,

DateT[Date] <= vSdate ,

DateT[Date] <= vLDate)

 

The Reuslt is still non-stoping, even though I added the line "DateT[Date] <= vLDate

Snap50.jpg

 

 

 

 

 

 

 

 

 

I would like to achieve  some of the following graph. Gray line is running total turnover for Same month last year, per day.  yellow line is running toal turnover for current month.  1-29 on axis are the days in current month. 

 

Snap51.jpg

 

Anonymous
Not applicable

BTW, which tool do you use to paste codings / pictures,

so it is not that large and looks nice and clean 🙂 

 

Anonymous
Not applicable

Hi @Anonymous ,

 

For Last year you can just create another metric like below.

I am using "insert code" option when replying and set language to C++ 😉

 

Measure test now =

Var vLDate= Date(Year(NOW())-1,Month(now()),31)
Var vSdate= Max(DateT[Date])
Var vStartDate = Date(year(vLDate),MONTH(vLDate),1)

Return

CALCULATE( SUM('Sellin Current Month CY'[Turnover (value/+) (CUR)])/1000000,

ALL(DateT),

DateT[Date] >= vStartDate,
DateT[Date] <= vLDate)

 

Anonymous
Not applicable

Hi @Anonymous ,

 

Thanks for spending time on this .

I just want to inform, the following approch do not work. 

Calculate ( xxx, 

[Date] >=LDate,

[Date] <=LDate, xxx )

 

We need to restrict different Time dimention in order to achive my purpose. 

I finally found the solution in the folloing post. 

https://community.powerbi.com/t5/Desktop/Running-total-graph-MTD-vs-Last-year-Month-with-cut-off-line-for/m-p/929047#M445229

 

But thanks for trying and spending time on it,

Have a really good day! 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors