Ignore Filter on Month level with Summarize

[Morkil]

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

[mark_endicott · Accepted solution]

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.