DAX in getting Maximum Rate by Month

[likhithar]

Hello All,

I have Cost , Sales fact tables . Town,State and DateTable are Dimension Tables. My Total Value is Sales+Cost.

I have another table which is Rates Table where rates will be there on  month basis. 

I tried two ways in deriving DAX

Total= SUM(Sales[Sales)+SUM(Cost[Cost])

Total YTD = CALCULATE([Total],DATESYTD(DateTable[Date]))

Total with Rate = SUMX(RatesTable,RatesTable[Rate])*[Total YTD]

Rates Table and Sales,Cost Table are related based on TownID+StateID+YYYYMM.The Rates will be at Town,State Level.

If I select 202102 , Total should get multiplied with feb Rate , If I select 202101 , Total should get multiplied with Jan Rate

With the above modeling and DAX , I'm the output as= Total YTD*JanRate+TotalYTD*FebRate.

• I tried another Data Modeling where I give relationship to Rates Table with Dimension Tables itself instead of Fact Table(means RatesTable[TownID]-Town[TownID] ; RatesTable[StateID]-State[StateID] ; RatesTable[YYYYMM]-DateTable[YYYYMM] 
• With the second Modelling I'm getting correct output by rows but total is showing wrong
• Total with Rate = SUMX(RatesTable,RatesTable[Rate])*[Total YTD]
• 

  There will be a dimension column which is Concatenation of Country+State+Town , against that dimension I will be projecting Total with Rate .

  Help me out on this.How to Sort it out?? TIA

[Anonymous]

Hi  @likhithar ,

Here are the steps you can follow：

1. Create calculated table.

Table = SUMMARIZE('Rates Table','Rates Table'[YYYMM],"1",MAX('Rates Table'[Rate]))

2. Create measure.

Flag =
var _table=SUMMARIZE('Table','Table'[YYYMM],"value",SUM('Table'[1])*[TotalYTD])
return
SUMX(_table,[value])

3. Result:

Best Regards,

Liu Yang

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