Forum Discussion

binayjethwa's avatar
binayjethwa
Helper V
3 years ago
Solved

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.

 

Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    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

     

2 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Community Support
    3 years ago

    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

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    3 years ago

    Hi,

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