Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
binayjethwa
Helper IV
Helper IV

Edit Sub totals using dax

Hi , 

 

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

 

Region June'23July'23Aug'23Sep'23Oct'23Nov'23Dec'23Jan'24Feb'24Mar'24Apr'24May'24Jun'24FY 24
India1001111121131141151161171181191201211221398
Srilanka1011121131141151161171181191201211221231410
Malaysia1021131141151161171181191201211221231241422
singapore1031141151161171181191201211221231241251434

 

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.

 

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

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

 

View solution in original post

2 REPLIES 2
Ashish_Mathur
Super User
Super User

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
v-yueyunzh-msft
Community Support
Community Support

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

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.