Edit Sub totals using dax

[binayjethwa]

Hi , 

Basically below is the output that i require to show , which is sales of countries for  current fiscal yeat Month wise.

Region	June'23	July'23	Aug'23	Sep'23	Oct'23	Nov'23	Dec'23	Jan'24	Feb'24	Mar'24	Apr'24	May'24	Jun'24	FY 24
India	100	111	112	113	114	115	116	117	118	119	120	121	122	1398
Srilanka	101	112	113	114	115	116	117	118	119	120	121	122	123	1410
Malaysia	102	113	114	115	116	117	118	119	120	121	122	123	124	1422
singapore	103	114	115	116	117	118	119	120	121	122	123	124	125	1434

FY 24 column below is just the subtotals i have enabled and renamed it.  Logically for FY 24 i should calculate sume of ( July'23- June 24) but since i have a column for June'23 , my totals is considering Jun'23 also which is not correct. 

Also my calendar is a rolling calendar , it will consider , current month and next 11 months , so i could have the values coming for Current fiscal and previous fiscal , also current fiscal and Next fiscal . But ultimately i need to show the totals of current fiscal year only .

For Example : For India , FY 24 is showing 1398 , ideally it should show 1298 since June'23 is not FY 24. Since this is a matrix visual i cannot create a separate measure and drag to values as i will have repeating value for each month separately. Is there any way we can achieve this, Please help.

[Ashish_Mathur]

Hi,

Not sure how much i can help but i can try.  Share the download link of the PBI file.

[v-yueyunzh-msft · Accepted solution]

Hi ,  @binayjethwa 

According to your description, you want to add a Sub total as the column in your matrix visual.

For your needs, you need to recreate a dimension table for the Column field, such as:

Table  =UNION( VALUES('Table'[Year_month]) , "FY 24")
Sure , you can also add a rank column to sort the column field.

Then we can use this Table's column as the Matrix visual's column field.

Then we need to create a measure , use MAX('Table'[Column]) to get the value of the current column header, and then use the IF() and  return the corresponding value according to different situations to meet your needs.

Secondly, according to your description, I don't really understand your company's fiscal year policy, and I don't have your test data, it is difficult to write dax according to different screening contexts, (July'23- June 24) refers to July every year To the value of June, if it is currently July'24, that is the value calculation (Aug'23-July'24)?And the July'23 means the 2023/7/1 ; June 24 means the 2024/6/1?

However, these are all implemented in the measurement value according to your business needs. For commonly used functions, you can consider using EDATE, EOMONTH(), FILTER(), SUMX() to achieve your needs.

Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

Best Regards,

Aniya Zhang

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