Projection Calculation

[Sachet_716]

Good afternoon!

I hope i can ask another question again. 

I would like to have a measure to compute projected sales for the entire month considering the MTD run rate. However, I cant see anything that pertains to that?

For example, MTD sales is 25,000 (as of 27th Nov), I am expecting to have 27,777 as result when I compute the 'landing sales' for the month.

Formula: (25,000/27) * 30  = 27,777

Rgds, Cristina

[Anonymous]

@Sachet_716,

Create a measure in your table using DAX below.

landing sales = ( [MTD Sales]/DAY(MAX(DateDim[Date])))*DAY(EOMONTH(MAX(DateDim[Date]),0))

Regards,
Lydia

[Sachet_716]

Thanks Lydia,

I made use of the measure but it returns with the same MTD sales?

Rgds, Cristina

[Anonymous]

@Sachet_716,

Do you need to get data based on MTD /today's day * days of current month? If so, create a column as follows.

today= Today()

Then replace datekey field with the today field in the landing measure.

Regards,
Lydia

[Sachet_716]

Thank you lydi, will try this out.   🙂

[jthomson]

The way I've done this is on my date table to have a couple of columns, one returning a 1 if the date is in the current month, and another returning a 1 if the date is in the past, and then having measures to sum them, the first one on its own, the second conditional on the first - that'll give you the figures you need to get your projections.

You could probably also approach it using the datediff function and combinations of today and eomonth as an alternative

[Sachet_716]

Thank you, do you mind sharing the formula?  😞

Thanks, Cristina