Forum Discussion

Papillon's avatar
Papillon
Frequent Visitor
2 years ago

Running Total in case value does not exist

Good Morning community

another topic I'm struggeling with. Hope anybody can help me to GET OUT OFT THIS! 😉

I have two related tables:

Table_1: "Antraege[KW nach Lieferung]"

Table_2: "KPI_Einzelfälle_Gesamt"

Relation 1:M

 

My measure "month_8" is to sum up the running total for "Fall-Zaehler" by "KW nach Lieferung".:

____________
VAR last_full_month = EOMONTH(max(Kennzahlen_Datenquellen[Last modified (Truck origin)]),-1)
VAR Startdate = EOMONTH(last_full_month,-9)+1 --ab dem ersten Tag des Monats
VAR Enddate = EOMONTH(last_full_month,-8) -- bis zum letzten Tag des Monats

VAR test_2 = Calculate(sum('KPI_Einzelfälle_Gesamt'[Fall-Zaehler]),
                     DATESBETWEEN('KPI_Einzelfälle_Gesamt'[Bandauflagedatum],Startdate,Enddate)
                    ,'KPI_Einzelfälle_Gesamt'[MIS_FZG_Rep*] <= 12
                    ,Antraege[KW nach Lieferung]<=SELECTEDVALUE(Antraege[KW nach Lieferung])
                    )

____________
As far as there are values in Antraege[KW nach Lieferung] it works, but if there there can't be found a row 

for SELECTEDVALUE(Antraege[KW nach Lieferung]) the measure, delivers "1".  Instead of this "1", I expect to receive the latest result avialaible. For instance: 

For TABLE_1[Antrage[KW nach Lieferung] 9, I expect 3, for 10 I expect 3,...for 18 I expect 14,..... 

 

 


Can anybody help me here? I'm struggeling already for hours...

Thx in advance.

Need Help

4 Replies

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    Hi Papillon 

    Maybe in your case new feature (in preview) is ok, check link 

    • Running sum. Calculates the sum of values, adding the current value to the preceding values. Uses the RUNNINGSUM function.
    • And this link
    • Papillon's avatar
      Papillon
      Frequent Visitor
      2 years ago

      Hi some_bih , thanks! Yes I agree. This would most likely help. But I have no glue when the new version will be avialable for me on one hand, and second,I just want to know which function(s) could provide the solution within a measure. I still hope, someone will let me know how to solve that. Regards

  • some_bih's avatar
    some_bih
    Community Champion
    2 years ago

    Hi Anonymous feature could be activate in newest pbi version on menu 

    File>>>Option and seeting >>>Options

    Close and open again pbi file

     

  • Papillon's avatar
    Papillon
    Frequent Visitor
    2 years ago

    Last try. 😉 Nobody in the forum that could give me a hint for a solution within a measure? I need a solution independent from a visual, as I need to use the result as well in a further measure. Thank you experts!