topic Re: Last 12 month sold for working days only in DAX Commands and Tips

Last 12 month sold for working days only

Anonymous — Fri, 05 Jul 2024 14:22:28 GMT

Hi, I want to calculate the sales/square meter for the past 12 months. But i need to edit two measures that talk each other.

I wrote this function to calculate the sales for the last 12 months, and it works:

Last 12 month=

CALCULATE (

[Sold],

TREATAS ( CALENDAR ( EDATE ( TODAY (), -12 ), TODAY () - 1 ), Time[Date] )

)

I have a Sold/Square meter measure, which is written like this and takes into account ONLY the days worked out of 365

Sold/MQ 365 =
CALCULATE( [Sold] / 365*365/[Sold Area],
FILTER( VALUES( ANCLI[Sales Point ID] ), [Sales Area] > 0&&[Sold]<>BLANK()))

But writing [Sold]/365, it's wrong because i want ONLY the days on which a store has sold

But how do I modify this last one so that it takes into account ONLY the days on which a store has sold?

Basically i want this replicate this formula
((sold/n° working days)*365)/Sold Area
into measure. How i do that?

After edited last formula, now i should write

Last 12 Month =

CALCULATE (

Sold/MQ 365,

TREATAS ( CALENDAR ( EDATE ( TODAY (), -12 ), TODAY () - 1 ), Time[Date] )

)

Thank you for your help

Re: Last 12 month sold for working days only

mark_endicott — Mon, 08 Jul 2024 08:26:50 GMT

Anonymous - In order to get an exact answer you need to post more about your model or some sample data.

But, to provide some help that maybe you can use. creating a measure / variable that's something along the lines of:

CALCULATE( DISTINCTCOUNT( 'Table'[Date Column] ), Filter( Table, [Sales] > 0 )

This would then replace 365.

If this helps or works, please accept as the solution.

Re: Last 12 month sold for working days only

mark_endicott — Mon, 08 Jul 2024 08:27:55 GMT

Anonymous - did this work? If yes, it would be great if you can mark it as the solution for ohters visibility!

Thanks!

Re: Last 12 month sold for working days only

Anonymous — Tue, 09 Jul 2024 10:59:43 GMT

Hi, thank you but i got the solution by my self.
First i calculated working days so:

Working Days =

COUNTROWS(

SUMMARIZE(

TableSold

,Time[Date]

)

)

Then, i created a measure to have sold for last 12 month, taking in count only these working days.

Sold/MQ 365 = (([Venduto]] /[Working Days])*365)/[MQ]