Sum of Daily Changes into Months and Financial Year

[santoshlearner2]

Dear All,

I have a table where there is calculated column, datewise it does the daily balance, which is nothing but deducting the currently value with the previous, this number i have on a daily basis, how to get a calculated column or a measure which will give me the respective monthly total ( of the daily values ) and the financial year total in a seperate column (financial year starts from March).

Please note the table is divide into category and sub category wise, hence need to calculate those respective values. Daily i am getting but monthly and Financial year is giving an error.

thank you all.

Date	Store	Main Product	Sub Product	Final sales  is from a measure (basicaly deducting previous date and current date)
31-01-2024	Delhi	Bike	Tyres	100
31-01-2024	mumbai	Bike	Chain	154
31-01-2024	goa	Bike	Bucket	160
31-01-2024	bandra	Car	Windshield	210
31-01-2024	Khar	Car	Accessories	230
31-01-2024	Nagpur	Car	Pencil	260
31-01-2024	North	Rulers	Small	100
31-01-2024	South	Rulers	Big	154
31-01-2024	Chennai	Rulers	Medium	160
30-01-2024	Delhi	Bike	Tyres	210
30-01-2024	mumbai	Bike	Chain	230
30-01-2024	goa	Bike	Bucket	260
30-01-2024	bandra	Car	Windshield	100
30-01-2024	Khar	Car	Accessories	110
30-01-2024	Nagpur	Car	Pencil	155
30-01-2024	North	Rulers	Small	156
30-01-2024	South	Rulers	Big	210
30-01-2024	Chennai	Rulers	Medium	260
28-01-2024	Delhi	Bike	Tyres	78
28-01-2024	mumbai	Bike	Chain	452
28-01-2024	goa	Bike	Bucket	460
28-01-2024	bandra	Car	Windshield	450
28-01-2024	Khar	Car	Accessories	136
28-01-2024	Nagpur	Car	Pencil	156
28-01-2024	North	Rulers	Small	850
28-01-2024	South	Rulers	Big	1230
28-01-2024	Chennai	Rulers	Medium	120

[v-junyant-msft · Accepted solution]

Hi @santoshlearner2 ,

Please check if this is what you want? I have calculated the sum of daily balance for each month and each fiscal year for different combinations of Main Product and Sub Product.
Here is the sample data:
DateMain ProductSub ProductFinal sales

2023.1.31	Bike	Tyres	10
2023.1.31	Bike	Chain	20
2023.1.31	Car	Windshield	30
2023.1.31	Car	Accessories	40
2023.1.15	Bike	Tyres	50
2023.1.15	Bike	Chain	60
2023.1.15	Car	Windshield	70
2023.1.15	Car	Accessories	80
2023.3.31	Bike	Tyres	90
2023.3.31	Bike	Chain	100
2023.3.31	Car	Windshield	110
2023.3.31	Car	Accessories	120
2023.3.15	Bike	Tyres	130
2023.3.15	Bike	Chain	140
2023.3.15	Car	Windshield	150
2023.3.15	Car	Accessories	160
2023.5.31	Bike	Tyres	170
2023.5.31	Bike	Chain	180
2023.5.31	Car	Windshield	190
2023.5.31	Car	Accessories	200
2023.5.15	Bike	Tyres	210
2023.5.15	Bike	Chain	220
2023.5.15	Car	Windshield	230
2023.5.15	Car	Accessories	240
2023.7.31	Bike	Tyres	250
2023.7.31	Bike	Chain	260
2023.7.31	Car	Windshield	270
2023.7.31	Car	Accessories	280
2023.7.15	Bike	Tyres	290
2023.7.15	Bike	Chain	300
2023.7.15	Car	Windshield	310
2023.7.15	Car	Accessories	320
2023.9.30	Bike	Tyres	330
2023.9.30	Bike	Chain	340
2023.9.30	Car	Windshield	350
2023.9.30	Car	Accessories	360
2023.9.15	Bike	Tyres	370
2023.9.15	Bike	Chain	380
2023.9.15	Car	Windshield	390
2023.9.15	Car	Accessories	400
2023.11.30	Bike	Tyres	410
2023.11.30	Bike	Chain	420
2023.11.30	Car	Windshield	430
2023.11.30	Car	Accessories	440
2023.11.15	Bike	Tyres	450
2023.11.15	Bike	Chain	460
2023.11.15	Car	Windshield	470
2023.11.15	Car	Accessories	480
2024.1.31	Bike	Tyres	490
2024.1.31	Bike	Chain	500
2024.1.31	Car	Windshield	510
2024.1.31	Car	Accessories	520
2024.1.15	Bike	Tyres	530
2024.1.15	Bike	Chain	540
2024.1.15	Car	Windshield	550
2024.1.15	Car	Accessories	560
2024.3.31	Bike	Tyres	570
2024.3.31	Bike	Chain	580
2024.3.31	Car	Windshield	590
2024.3.31	Car	Accessories	600
2024.3.15	Bike	Tyres	610
2024.3.15	Bike	Chain	620
2024.3.15	Car	Windshield	630
2024.3.15	Car	Accessories	640
2024.5.31	Bike	Tyres	650
2024.5.31	Bike	Chain	660
2024.5.31	Car	Windshield	670
2024.5.31	Car	Accessories	680
2024.5.15	Bike	Tyres	690
2024.5.15	Bike	Chain	700
2024.5.15	Car	Windshield	710
2024.5.15	Car	Accessories	720
2024.7.31	Bike	Tyres	730
2024.7.31	Bike	Chain	740
2024.7.31	Car	Windshield	750
2024.7.31	Car	Accessories	760

Use these DAXs to create two calculated columns:

monthly total = 
VAR _CurrentMonth = MONTH([Date])
VAR _CurrentYear = YEAR([Date])
VAR _MainProduct = [Main Product]
VAR _SubProduct = [Sub Product]
RETURN
CALCULATE(
    [daily balance],
    FILTER(
        ALL('Table'),
        MONTH('Table'[Date]) = _CurrentMonth && YEAR('Table'[Date]) = _CurrentYear && 'Table'[Main Product] = _MainProduct && 'Table'[Sub Product] = _SubProduct
    )
)

financial year total = 
VAR _CurrentMonth = MONTH([Date])
VAR _CurrentYear = YEAR([Date])
VAR _MainProduct = [Main Product]
VAR _SubProduct = [Sub Product]
VAR Monthlessthan3 = 
CALCULATE(
    [daily balance],
    FILTER(
        ALL('Table'),
        'Table'[Date] >= DATE(_CurrentYear - 1, 3, 1) && 'Table'[Date] < DATE(_CurrentYear, 3, 1) && 'Table'[Main Product] = _MainProduct && 'Table'[Sub Product] = _SubProduct
    )
)
VAR Monthmorethan3 = 
CALCULATE(
    [daily balance],
    FILTER(
        ALL('Table'),
        'Table'[Date] >= DATE(_CurrentYear, 3, 1) && 'Table'[Date] < DATE(_CurrentYear + 1, 3, 1) && 'Table'[Main Product] = _MainProduct && 'Table'[Sub Product] = _SubProduct
    )
)
RETURN
IF(
    _CurrentMonth < 3,
    Monthlessthan3,
    Monthmorethan3
)

And the final output is as below:

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

[santoshlearner2]

Hello Dino,

Thank you for your reply, appreciate your help. There is one issue, what is happening is for monthly change, it sums all the days and gives a higher figure than the actual for example Jan-23 if it s360 it shows 720.

[v-junyant-msft]

Hi @santoshlearner2 ,

I apologize that I may not have understood you well. Is this reply of yours talking about my provided DAX and sample data?
You said “for example Jan-23 if it s360 it shows 720”, which seems to mean that the sum of all the data corresponding to Jan-23 in my sample data is equal to 360, but didn't you say in your original requirement that you want to “divide into category and sub category wise, hence need to calculate those respective values”, please confirm what exactly is your Monthly total calculation?

Best Regards,
Dino Tao