Forum Discussion

Thejeswar's avatar
Thejeswar
Super User
8 years ago
Solved

Filter with calculated measures

Hello guys, I have been trying to create a calculated measure which should be filtered based on another measure. Here's my scenario   1. I want to create Total YTD of a column which I am doing by ...
Need Help
  • Floriankx's avatar
    Floriankx
    8 years ago

    Hello,

     

    What probably would work perfectly is a separate DateTable with contiguous Dates. Then you could add the DateTable columns to your PivotTable and use it for TOTALYTD.

     

    After trying a little bit this formula gave me the correct result as well:

    YTD Revenue:=VAR LatestDate=MAX(FactTable[Datekey])
RETURN
CALCULATE(SUM([column]);
     CALCULATETABLE(
      FILTER(FactTable;FactTable[Datekey]<=LatestDate&&YEAR([Datekey])=YEAR(LatestDate));
      ALL(FactTable)
     )
    )

     

Thejeswar's avatar
Thejeswar
Super User
8 years ago

Hi,

The Measure gets filtered for the Month, instead of bringing all the data of months less than that month

 

Please refer the screenshot posted above in the post for the same

Floriankx's avatar
Floriankx
Solution Sage
8 years ago

Hello,

 

What probably would work perfectly is a separate DateTable with contiguous Dates. Then you could add the DateTable columns to your PivotTable and use it for TOTALYTD.

 

After trying a little bit this formula gave me the correct result as well:

YTD Revenue:=VAR LatestDate=MAX(FactTable[Datekey])
RETURN
CALCULATE(SUM([column]);
 CALCULATETABLE(
  FILTER(FactTable;FactTable[Datekey]<=LatestDate&&YEAR([Datekey])=YEAR(LatestDate));
  ALL(FactTable)
 )
)

 

  • Thejeswar's avatar
    Thejeswar
    Super User
    8 years ago

    This thing works fine. Can you pls. explain this DAX Usage?

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi, I have a similar question with text filter and I know it's pretty stupid but I am not able to figure out a simple thing to do in DAX.

    In the image above, I want to calculate Weighted Sales filtered on Types

    Current formula for Weighted Sales = Sales/ Sum(Sales)

    I am trying to do Weighted Sales = Sales/ [Sum(Sales), filtered by (Type)] - I want to see the weighted sales by types.

    Whenever I am using DAX, I need to use Divide function and for numerators and denominators in it, I have either use SUM() or similar funcitons to perform calculation, I cannot do simple Column1/Column2.

    Can anyone please help me in this?