Forum Discussion

Osmandfernanobi's avatar
Osmandfernanobi
Regular Visitor
6 years ago

Running sum with Period

Dear experts,    I have a situation where I wanted to show the running total in a line chart for Actuals, Expected, and Variance, we are able to get the values and create the visual. But having a s...
DAX
running total
ibarrau's avatar
ibarrau
Icon for Super User rankSuper User
6 years ago

Hi. I see. The idea of this is to recognize the "Stop Here" automatically. We can help you do it if you tell us more about it

Is actual in a separate table?

Is there only one table in the model?

 

For now I'll show an example without it. You can resolve this in two different ways. The first and most importante is to recognize automatically the "max_date" variable. Then you can just call the calculation before that date or complete with blank for the next days of that date:

 

Measure 1 = 
VAR max_date = DATE(2009;1;1)
RETURN
CALCULATE( [SalesAmount]; InternetSales[Order Date] < max_date )

Measure 2 =
VAR max_date = DATE(2009;1;1)
RETURN
IF(
    MAX(InternetSales[Order Date]) > max_date ;
    BLANK();
    [SalesAmount]
)

 

Build this measures for Budget and Variance (my sales amount should be your budget)

Hope this helps,

Regards,

osmandfernando's avatar
osmandfernando
Icon for Advocate I rankAdvocate I
6 years ago

Same Osmand here, I am confused with my community accounts, 

 

ibarrau 

Actual is not a separate table, All those 3 data fields (Actual, Expected, Variance) are in a new table created based on a another table name Burn, 

There are several tables as you can see below

 

 

Thank you

Osmand

  • ibarrau's avatar
    ibarrau
    Icon for Super User rankSuper User
    6 years ago

    Well, those measures are probably the "Sum" of something. Can you show us the columns involved in the tables that are used in the measures? Right there I can't see anything related with actual due to measures are not related with the table in which they are created.

     

    Regards,

    • osmandfernando's avatar
      osmandfernando
      Icon for Advocate I rankAdvocate I
      6 years ago

      Thank you for the reply

      following are the formulas 

       

       

      Actual = CALCULATE(SUM(Burn[ACTUAL]),FILTER(ALLSELECTED(Burn),Burn[TrxnDate]<=MAX(Burn[TrxnDate])))
      Variance = CALCULATE(SUM(Burn[VARIANCE]),FILTER(ALLSELECTED(Burn),Burn[TrxnDate]<=MAX(Burn[TrxnDate])))
      Expected = CALCULATE(SUM(Burn[BUDGET]),FILTER(ALLSELECTED(Burn),Burn[TrxnDate]<=MAX(Burn[TrxnDate])))
      • ibarrau's avatar
        ibarrau
        Icon for Super User rankSuper User
        6 years ago

        Try using one of my formulas changing the max_date like this:

        Measure =
VAR max_date = 
CALCULATE (
    MAX(Burn[TrxnDate]);
    FILTER(
        ALL(Burn[TrxnDate];Burn[ACTUAL]);
        OR( Burn[ACTUAL]>0 ; Burn[ACTUAL] <> BLANK() )
    )
)
RETURN
--Formula--

         That should be the max date of the actual values. Then you can use that as variable of the other formulas.

        Regards,