Aggregating multiple variables in DAX measure

[13havya]

Hi,

I am trying to build a measure that contains a couple of variables, and I need the totals row to be the sum of all values in the visual. 

The logic behind the metric - 

The business records a "cutoff" dataset on the 9th of every month. The cutoff dataset has a record of files opened in the past month (i.e cutoff recorded on 9 May will contain data for files opened in April).

I want to design the measure such that: 

Cutoff Files = 

IF(

DAY(TODAY) < 9,
RETURN CUTOFF (till 2 months ago) + live data from files opened in last month

)

This will ensure that all data till the previous month gets populated, regardless of whetehr the cutoff point has been reached or not. 

[Anonymous · Accepted solution]

Hi @13havya ,

Based on my testing, please try the following methods again:

1.Create the simple tables.

2.Create the new measure to calculate.

Cutoff files = 
var last_month = CALCULATE(SUM('Table'[Files]), FILTER('Table', MONTH('Table'[Date]) = MONTH(TODAY()) - 1))
VAR two_months = CALCULATE(SUM('Table'[Files]), FILTER('Table', MONTH('Table'[Date]) < MONTH(TODAY()) - 2))
RETURN
IF(DAY(TODAY()) < 9, last_month + two_months)

3.Drag the measure into the card visual. The result is shown below.

Best Regards,

Wisdom Wu

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