Calculating measure with multiple conditions (including previous months) for a card

[PierreCrunch]

Hello,

I want to calculate the sum of bank balances for a specific company (let's call it "Project Company") for the previous month(s) and put the balance in a card(s).  I cannot use a slicer or a table for my report page, so I want to put the calculated balance for Project Company in card(s).

To explain my data, the Project Company has different bank accounts, and for each month I want to sum the different bank balances to get the overall amount of balance for said month (e.g. for October 16,6M). The values for the newest month (here November) are updated the next month (now beginning December they're haven't been updated yet), which is relevant to know since I want to calculate the balance for the previous month (November) and the pre-previous month (October) in December as of writing this, so although November arguably will be the latest month here in the data set it is in real-time the previous month.

Company	Balance	Date
Project Company	10.000.000,00	31.01.2020
...	...	...
Project Company	6.300.000,00	30.09.2022
Project Company	5.400.000,00	30.09.2022
Project Company	5.900.000,00	30.09.2022
Project Company	5.300.000,00	31.10.2022
Project Company	5.600.000,00	31.10.2022
Project Company	5.700.000,00	31.10.2022

First, I constructed a measure that simply calculates the balance for the previous month(s).

Previous Month = CALCULATE(SUM('Table'[Balance]), 
                  PREVIOUSMONTH('Table'[DateTable].[Date]))

Using a table, where I insert the measure together with a date variable and a slicer set to Project Company shows the value for October in the November row and further correctly for the past (with the December row being non-existent). 

To specify that I want this for Project Company and inside a card, I simply inserted another filter specifying that the company is Project Company.

Previous Month = CALCULATE(SUM('Table'[Balance]), 
                  PREVIOUSMONTH('Table'[DateTable].[Date]), 
                        'Table'[Company] = "Project Company")

But now inserting the measure, I receive a BLANK for the card. I assume it's BLANK in the card, as I calculate for each month the value of the previous month, and since these are several values, I might not be able to put it in a card.

I used other approaches than PREVIOUSMONTH like

Previous ProNM = CALCULATE(SUM('Table'[Balance]), DATEADD('Table'[DateTable].[Date], -1, MONTH), 
             'Table'[Company] = "Project Company")

, and these give me the overall sum of the balance for Project Company in the card, but for all months since 2020 and not for previous month November (or October, when -2 specified).

I got closer with using 

Previous ProNM 3 = var current_month= MONTH(TODAY())
  return  
  CALCULATE(SUM('Table'[Balance]),
              FILTER('Table',MONTH('Table'[DateTable].[Date])=current_month -2 ||
                'Table'[Company] = "Project Company"))

, which I have seen in another post here, and this gives me the pre-previous month October (or November if -1) balance, but it includes the October from all years and not the one 2022, which I want.

A lot would probably advise to simply specify the exact date, e.g. with DATESBETWEEN

Balance October =
CALCULATE (
    SUM ( 'Table'[Balance] ),
    DATESBETWEEN (
        'Table'[DateTable].[Date],
        DATE ( 2022, 10, 30 ),
        DATE ( 2022, 11, 1 )
    )
)

But keep in mind, that I want to automize this report. Hence the measure should update according to the current month we are in, so manually changing the dates in this measure is not preferred. 

To conclude, I would like to have two cards, where one displays the sum of the balance for Project Company in November (previous month from December) and one for October (pre-previous month from December), which would adjust to one month further if we move to the next month, e.g. January. 

I'm thankful for any help or suggestion. 

[v-rongtiep-msft · Accepted solution]

Hi @PierreCrunch ,

I have created a  simple sample, please refer to it to see if it helps you.

Create columns.

month = MONTH(Sheet1[date])

year = YEAR(Sheet1[date])

Then create measures.

displays the sum of the balance in November

Measure =
VAR _1month =
    MONTH ( EDATE ( TODAY (), -1 ) )
VAR _yyear =
    YEAR ( EDATE ( TODAY (), -1 ) )
RETURN
    CALCULATE (
        SUM ( Sheet1[value] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[month] = _1month && Sheet1[year] = _yyear )
    )

In you sample , you need to add  Project Company.

Measure =
VAR _1month =
    MONTH ( EDATE ( TODAY (), -1 ) )
VAR _yyear =
    YEAR ( EDATE ( TODAY (), -1 ) )
RETURN
    CALCULATE (
        SUM ( Sheet1[value] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[month] = _1month && Sheet1[year] = _yyear&&sheet1[Company]="Project Company" )
    )

Pay attention to letter case.

Measure2 =
VAR _1month =
    MONTH ( EDATE ( TODAY (), -2 ) )
VAR _yyear =
    YEAR ( EDATE ( TODAY (), -1 ) )
RETURN
    CALCULATE (
        SUM ( Sheet1[value] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[month] = _1month && Sheet1[year] = _yyear )
    )

 In you sample , you also  need to add  Project Company.

Measure2 =
VAR _1month =
    MONTH ( EDATE ( TODAY (), -2 ) )
VAR _yyear =
    YEAR ( EDATE ( TODAY (), -1 ) )
RETURN
    CALCULATE (
        SUM ( Sheet1[value] ),
        FILTER ( ALL ( Sheet1 ), Sheet1[month] = _1month && Sheet1[year] = _yyear&& Sheet1[year] = _yyear&&sheet1[Company]="Project Company" )
    )

How to Get Your Question Answered Quickly 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[PierreCrunch]

Hello,

sorry for the late answer. In the end we had to finish a first version of the report, so we went with a non-dynamic solution. Now I was able to check out your response, and it worked out for me! Will implement it now.
Thanks for the help!

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523