DAX question: How to calculate YTD of the ratio of two measures.

[mkjit256]

Hello everyone.
Consider the following scenario:
I have two fact tables, where i am building two measures A & B such that:

A = Calculate(sum(Table1[Column1])
B = Calculate(Count(Table2[ColumnA]), Table2[ColumnB]="ValueB", Table2[ColumnC] = "ValueC")

I have also defined a measure C as:

C =  if(B>1000000 , DIVIDE(A*1000000,B), DIVIDE(A*100000,B))

and has visualized the results in a Matrix Visual.

The problem that i am having begins when i try to calculate the  YTD of measure C, as follows:

YTD_C = TOTALYTD(C,Date[Date])

What is actually happening in YTD_C is Sum(A_i)/Sum(B_i) instead of sum(A_i/B_i)

For example the YTD_C for 2013 -03-01 currently calculated is (A1+A2+A3)/(B1+B2+B3) but what i am looking to have is:
(A1/B1 + A2/B2 + A3/B3)

how can i achieve that? 

@Greg_Deckler, any guidnance or thoughts would be really appreciated.

[Dangar332]

hi, @mkjit256 

try below measure

measure=
CALCULATE(
   [c],
   DATESYTD('date'[date]),
   ALLEXCEPT('date','date'[date])
 )

[mkjit256]

Thanks for your reply. It is giving the same thing as the YTD_C

YTD_C = TOTALYTD(C,Date[Date])

[Jihwan_Kim]

Hi,

I am not sure how your semantic model looks like, but please try something like below.

expected result measure: =
SUMX (
    WINDOW (
        1,
        abs,
        0,
        rel,
        ALL ( CalendarTable ),
        ORDERBY ( CalendarTable[datecolumn], ASC ),
        ,
        PARTITIONBY ( CalendarTable[year] )
    ),
    measure_C
)

[mkjit256]

Hello Kim,

Thanks for your reply, the scemantic layer that i am using is very simple,

Table A and B are my fact tables, and i have some additional dimension tables that are all in 1 to many relationship with the fact tables.

I have tried the measure that you have provided but it is not giving what i am expecting, and i am explain the numbers. here is what i am getting.