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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Morkil
Helper I
Helper I

Ignore Filter on Month level with Summarize

I have a requirement where I have to do the currency conversion for my measure on row level and add them up to get correct data on top levels . Data in my  'Balance Sheet Measures', is available for only July month of each year  and because of this I can see the Numbers in July Month only in matrix. Is there any way I can see the July amount in each month , even though it doesn't exist. Basically I want July amount in each Month of that year. I have done summarize here and then Sumx so that different conversion happen on row level and I want to see the sum of those on Month Level.

 

This is the DAX I have used

 

 

VAR BSBalance =

   CALCULATETABLE (

       ADDCOLUMNS (

           SUMMARIZE (

               'Balance Sheet Measures',

               'Date - Report'[DETL_ReportDateSurrogateKey],

               'Currency - Local'[Currency Code]

           ),

           "CBSBalanceAmount", [Balance Sheet Actual Amount] ,

           "CRate", CALCULATE ( AVERAGE ( 'Currency - Exchange Rate'[Currency Rate Amount] ) )),       

               KEEPFILTERS ( TREATAS ( { "M" }, 'Currency - Exchange Rate'[Rate Type Code] ) )

   )

VAR BSResult =

   SUMX (

       BSBalance ,

       IF (

            NOT ISBLANK ( [CRate] ) ,

           [CBSBalanceAmount] / [CRate],

           BLANK ()

       )

   )

RETURN

   BSResult

 

 

 

Morkil_0-1715832168161.png

 

1 ACCEPTED SOLUTION
mark_endicott
Super User
Super User

I am assuming you have a relationship between 'Date - Report'[DETL_ReportDateSurrogateKey] and a date column in your currency table.

 

One way to solve this would be to create a "Day of Month" column in each, and use this as your relationship and inside the summarize. This would make it agnostic of the Month. 

 

If you have multiple years, you would just need to add this into the column you create. It would result in a many to many join, so would need to be tested. But it's the simplest route without getting into over complex DAX. 

View solution in original post

1 REPLY 1
mark_endicott
Super User
Super User

I am assuming you have a relationship between 'Date - Report'[DETL_ReportDateSurrogateKey] and a date column in your currency table.

 

One way to solve this would be to create a "Day of Month" column in each, and use this as your relationship and inside the summarize. This would make it agnostic of the Month. 

 

If you have multiple years, you would just need to add this into the column you create. It would result in a many to many join, so would need to be tested. But it's the simplest route without getting into over complex DAX. 

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.