Running Monthly Total of Passing Scores Given a Previous Constant

[Vroom_Master3]

Hello all. 

I have a data set that combines a lot of Audits. This data is also just now moving to Automation, so I'm having to deal with a previous total. 

This comes together in two parts. I have a "Constant" variable and the actual calculation. 

The "Constant" : 

If(AND(Min('Table'[Month]) = 'Table'[Month], Min('Table'[Year] = 'Table'[Year])), 83,Calculate( ??? ))

This checks that it is the first month of data, and if it is, passes my previous constant from manual collection 83. If it isn't, it is supposed to calculate the Max or average of the previous month's value for this column. This is stored as your constant. For the first month in data collection it is basing its total off of this value. Then it adds this value, whether first month or a month after, to the total of audits in that month that score above a certain grade. That I also have problems with. I need all the values in the current month that pass my two filters to be counted and added to the constant. 

Var Constant
Return Constant + Calculate(CountRows('table'[ID]),Filter('table', ???)

\\\ 'table'[Score] >= 72 , 'table'[Audit_Type] = "Certification" 

 This should in theory be the same value for every row in a month so I can easily reference it in a month graph. I do not know how to reference all values in month from a row in calculated column, nor do i know how to reference previous data in the column I am currently in.  

This could also be a faulty line of thinking, it took me forever to come up with this way to solve my problem, but there might be another way that avoids these two problems with DAX that I am having. Or there is simple enough DAX out there that this can be implemented. I am completely unsure, but I would greatly appreciate some help. 

[Vroom_Master3 · Accepted solution]

I figured it out. 

I made a calculated column that returns 1 or 0 based on hitting all my filters

Bronze Cert = If(
    AND(
        CONTAINSSTRING(
            'Table'[Type of Audit], 
            "Certification"
        ),
        'Table'[Sum Scores] >= 72
    ),
    1,
    0
)

Then I did a measure that does a running total with some logic for adding my previous number in an if statement. 

## Audit RT = Calculate(
     SUM(
        'Table'[Bronze Cert]),
         Filter(
            All(
                'Table'
            ),
                'Table'[Date of Audit] <= max('Table'[Date of Audit]
            )
         ),
     'Table'[Area] = "External"
) + IF(
        MIN(
            'Table'[Date of Audit]
         )
        > Date(2024,1,31), 9, blank()  //This is set to just before my data started so that I dont add 9 to every month in my date table. 
     )

[Ashish_Mathur]

Hi,

Not sure of how much i can help but i would like to try.  Share some data to work with and show the expected result in a simple Table format.

[Vroom_Master3]

THanks! Here is some data.

Date	Month	Year	Audit Type	Score
24-Jan	January	2024	Full	68
30-Jan	January	2024	Cert	72
31-Jan	January	2024	Cert	80
2-Feb	February	2024	Full	59
4-Feb	February	2024	Cert	96
6-Feb	February	2024	Full	48
14-Feb	February	2024	Full	18
18-Feb	February	2024	Cert	58
19-Feb	February	2024	Full	64
25-Feb	February	2024	Cert	69
1-Mar	March	2024	Full	78
4-Mar	March	2024	Full	75
5-Mar	March	2024	Cert	84
7-Mar	March	2024	Full	91
15-Mar	March	2024	Full	53
18-Mar	March	2024	Cert	95
30-Mar	March	2024	Full	48
2-Apr	April	2024	Full	75
10-Apr	April	2024	Cert	76
12-Apr	April	2024	Cert	90
16-Apr	April	2024	Full	100
22-Apr	April	2024	Cert	81

Here is the expected outcome given the previous constant and a passing score of 72. It is additive only when the Cert Audit has a score of 72 or more. 

Pre-Constant	68
Jan	69
Feb	70
Mar	72
Apr	75

[Ashish_Mathur]

Sorry but just cannot understand your question.

[Vroom_Master3]

Sorry. Okay. 

I'm moving some audits from on paper to automated. Part of data collection is the amount of sites that have certified. Before I started automating there was already a number of sites that were certified. So I need to make a running total of all sites that have certified so that I can represent it in a monthly bar graph that looks like this: 

In Power Bi I don't know how to make a running total that is reactive with a starting number. My automation starts recently so I have to import the '21' from above make a running count that is additive to the 21, only adding where the Type of audit was a certification and the score was above the passing grade. I used 72 as the passing score in my example data. 

[Vroom_Master3]

After trying some stuff, this is as far as I could get with the second problem, this adds everything up, but isnt exclusive to every month. just shows the total of all data. It exports the same value for every row. 

Var Constant
RETURN Constant + Calculate(
    Countrows('Table'),
    Filter(All('Table'),
        AND(Month('Table'[Date_of_Audit]) = 'Table'[Month], YEAR('Table'[Date_of_Audit]) = 'Table'[Year])), 

    FILTER('Table', 
        'Table'[Score] >=72 ), 
    Filter('Table', 
        CONTAINSSTRING('Table'[Type_Audit], "Silver Certification")))