Forum Discussion

likhithar's avatar
likhithar
Icon for Helper III rankHelper III
4 years ago

DAX in getting Maximum Rate by Month

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
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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