Adding Monthly Measures "NOT" with YTD DAX calcs

[Higgs11]

Hello all, 

I have a basic matrix table using the date table in the columns and a DAX measure as the values. This DAX measure is dividing two sets of sums from the main data table. My Current Month table is spot on, but the YTD is not calculating the way I need it to. 

I need a measure that will add each monthly measured results indivisually, and not the way DAX does it using any YTD measurement. 

If this was an excel spreadsheet, i would use Sum (A:E). It looks like the DAX YTD is using the YTD totals first for each measure before dividng the two, like this YTD(Salaries) / YTD (Headcount). I need (Jan (Salaries) / Jan (Headcount)) + (Feb (Salaries) / Feb (Headcount) )...etc

[Anonymous]

Hi @Higgs11 ,

Could you please provide more details with your desired output and pbix file without privacy information?

How to Get Your Question Answered Quickly 

Best Regards

Community Support Team _ Polly

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

[amitchandak]

@Higgs11 , if you want grand total to be YTD

If(isinscope(Table[Group]) && not(Isingroup(Date[Date])) , [Wages Rate], divide( CALCULATE(SUM(Table[Salaries]),DATESYTD('Date'[Date],"12/31")), CALCULATE(SUM(Table[Headcount]),DATESYTD('Date'[Date],"12/31")))

refer for isinscope

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[Higgs11 · Accepted solution]

Thank you for the response. I could not quite get it to work. What I ended up doing was adding each month together like this;

02 Lever YTD Force Salaries =

CALCULATE (

[02 Lever Force Salaries],

'Date'[YTD Month] = "Y",

'Date'[Month Number] = 1

) +

CALCULATE (

[02 Lever Force Salaries],

'Date'[YTD Month] = "Y",

'Date'[Month Number] = 2

) +

CALCULATE (

[02 Lever Force Salaries],

'Date'[YTD Month] = "Y",

'Date'[Month Number] = 3

) +....until 12

I then removed the month category out of the column in the Matrix table. I then added a switch filter to change the table to current month, prior month, YTD, etc. 

02 Non Dev Force Salaries =

SWITCH (

[View YTD Number],

1, DIVIDE ( CALCULATE( [02 Lever Force Salaries], 'Date'[Current Month] = "Y" ),1000000, 0 ) ,

2, DIVIDE( [02 Lever YTD Force Salaries] , 1000000 , 0 ) ,

3, DIVIDE ( CALCULATE( [02 Lever Force Salaries], 'Date'[Prior Month] = "Y" ),1000000, 0 )

)

My switch table is 

My filter on the page looks like this 

Thank you all for the help 😊