Mistery of Measure with no totals

[CostaM2018]

I stuck with a measure that doesn't seem to sum totals at the end. If anyone can help me would be much appreciated. 
I have two measures that is used to create a third measure

BP Trxs # = IF(
SUM('Monthly Revenue Report'[Bill Payment # Trx]) = 0,
BLANK(),
SUM('Monthly Revenue Report'[Bill Payment # Trx]))

BP Trxs # LY = CALCULATE([BP Trxs #], SAMEPERIODLASTYEAR('dCalendar'[Date]))

LossOfBP# (before_billing) =
VAR CurrentYearTRX = [BP Trxs #]
VAR PrevYearTRX = [BP Trxs # LY]
RETURN
IF (
ISBLANK(CurrentYearTRX) && NOT(ISBLANK(PrevYearTRX)),
PrevYearTRX, -- Return the value of the measure directly
BLANK()
)

ISSUE:
measure does a good job indentifying the Loss per customer_number but total is always blank. 

"LossOfBP# (before_billing)" should sum at the bottom for each column

sample
Fiscal Year and Fiscal Month is coming form Calendar table [Date]

Fiscal Year	Fiscal Month	customer_number	BP Trxs # LY	BP Trxs #	LossOfBP# (before_billing)
2025	5	a1	96	105
2025	5	a2	5	8
2025	5	a3	221	209
2025	5	a4	17	21
2025	5	a5	3		3
2025	5	a6		8
2025	5	a7	4	3
2025	5	a8	7	11
2025	6	a1	59		59
2025	6	a2	57	38
2025	6	a3	40	37
2025	6	a4	7	17
2025	6	a5	17	15
2025	6	a6	8	11
2025	6	a7	59	23
2025	6	a8	8	9
2025	7	a1	36	26
2025	7	a2	5	4
2025	7	a3	1		1
2025	7	a4	38	14
2025	7	a5	126	115
2025	7	a6	6		6
2025	7	a7	2	1
2025	7	a8	12	16
2025	7	a9		12
2025	8	a1	3	7
2025	8	a2	18	24
2025	8	a3	1		1
2025	8	a4	10	12
2025	8	a5	13	6
2025	8	a6	2	2
2025	8	a7	7	5
2025	8	a8	21	15
2025	8	a9	48	74
2025	8	a10	53	58
2025	8	a11		3
2025	8	a12	1	1
2025	8	a13	4	3
2025	8	a14	16		16

[DataInsights]

@CostaM2018,

Try wrapping your measure in SUMX using the dimensions in your visual. Example:

LossOfBP# (before_billing) =
SUMX (
    SUMMARIZE ( dCalendar, dCalendar[Fiscal Year], dCalendar[Fiscal Month] ),
    VAR CurrentYearTRX = [BP Trxs #]
    VAR PrevYearTRX = [BP Trxs # LY]
    RETURN
        IF (
            ISBLANK ( CurrentYearTRX ) && NOT ( ISBLANK ( PrevYearTRX ) ),
            PrevYearTRX, -- Return the value of the measure directly
            BLANK ()
        )
)

[CostaM2018]

Hello, thank you for helping to sort this up with me. I tried to adjust the dax but I am having the same issue. I believe breaking my tables up would help you to understand what might be happening and maybe guide me to a different route. 

My intent with the dax is to use current Fiscal year qty transactions vs last Fiscal year qty Transactions to define loss in transactions per customer_number in current year. 

Conditional is something like:
In case the "current Fiscal" = Blank or null and "Last Fiscal" <> Blank or null, then "Last Fiscal", else Blank().

I was trying to do this with the dax but I bet there is something wrong in the way. 

here is a breakdown of
my tables:
table1: Monthly Revenue Report

PeriodEnd	customer_number	Bill Payment # Trx
2024-05-31	a1	105
2024-05-31	a2	8
2024-05-31	a3	209
2024-05-31	a4	21
2024-05-31	a5
2024-05-31	a6	8
2024-05-31	a7	3
2024-05-31	a8	11
2024-06-30	a1
2024-06-30	a2	38
2024-06-30	a3	37
2024-06-30	a4	17
2024-06-30	a5	15
2024-06-30	a6	11
2024-06-30	a7	23
2024-06-30	a8	9
2024-07-31	a1	26
2024-07-31	a2	4
2024-07-31	a3
2024-07-31	a4	14
2024-07-31	a5	115
2024-07-31	a6
2024-07-31	a7	1
2024-07-31	a8	16
2024-07-31	a9	12
2024-08-31	a1	7
2024-08-31	a2	24
2024-08-31	a3
2024-08-31	a4	12
2024-08-31	a5	6
2024-08-31	a6	2
2024-08-31	a7	5
2024-08-31	a8	15
2024-08-31	a9	74
2024-08-31	a10	58
2024-08-31	a11	3
2024-08-31	a12	1
2024-08-31	a13	3
2024-08-31	a14
2023-05-31	a1	96
2023-05-31	a2	5
2023-05-31	a3	221
2023-05-31	a4	17
2023-05-31	a5	3
2023-05-31	a6
2023-05-31	a7	4
2023-05-31	a8	7
2023-06-30	a1	59
2023-06-30	a2	57
2023-06-30	a3	40
2023-06-30	a4	7
2023-06-30	a5	17
2023-06-30	a6	8
2023-06-30	a7	59
2023-06-30	a8	8
2023-07-31	a1	36
2023-07-31	a2	5
2023-07-31	a3	1
2023-07-31	a4	38
2023-07-31	a5	126
2023-07-31	a6	6
2023-07-31	a7	2
2023-07-31	a8	12
2023-07-31	a9
2023-08-31	a1	3
2023-08-31	a2	18
2023-08-31	a3	1
2023-08-31	a4	10
2023-08-31	a5	13
2023-08-31	a6	2
2023-08-31	a7	7
2023-08-31	a8	21
2023-08-31	a9	48
2023-08-31	a10	53
2023-08-31	a11
2023-08-31	a12	1
2023-08-31	a13	4
2023-08-31	a14	16

table2: dcalendar

Date	Fiscal Year	Fiscal Month Sort Order	Fiscal Month
2023-05-31	2024	1	May
2023-06-30	2024	2	Jun
2023-07-31	2024	3	Jul
2023-08-31	2024	4	Aug
2023-09-30	2024	5	Sep
2023-10-31	2024	6	Oct
2023-11-30	2024	7	Nov
2023-12-31	2024	8	Dec
2024-01-31	2024	9	Jan
2024-02-29	2024	10	Feb
2024-03-29	2024	11	Mar
2024-04-30	2024	12	Apr
2024-05-31	2025	1	May
2024-06-30	2025	2	Jun
2024-07-31	2025	3	Jul
2024-08-31	2025	4	Aug
2024-09-30	2025	5	Sep

table 3: customer

customer_number

a1

a2

a3

a4

a5

a6

a7

a8

a9

a10

a11

a12

a13

a14

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[CostaM2018]

I have a pbix I can share. I dont have onedrive or any place I can drop the file. how can I send it to you? by email maybe?

[CostaM2018 · Accepted solution]

Hello @Ashish_Mathur 

I was able to make it work. I wrote the dax as follows:

BP#Loss =

SUMX(

    FILTER(

        VALUES('tbl_customer (2)'[customer_number]),

        ISBLANK([BP Trxs #]) && NOT(ISBLANK([BP Trxs # LY]))

    ),

    [BP Trxs # LY]

)

this sums the subtotal and totals of the result of conditionals. 

Thanks 
M