topic Re: Easy CALCULATED TABLE - Separate Monthly Sales into Day Amount in DAX Commands and Tips

Easy CALCULATED TABLE - Separate Monthly Sales into Day Amount

Power-CJ — Wed, 21 Jun 2023 08:32:16 GMT

Hello there,

i am stucking here in a issue, which i know that the solution is too easy.

I have a table with 3 Columns:

- articlenumber

- Month-Year

- salesamount

There is another Table i genarted with DAX with 2 Columns:

- articlenumber from the first table i mentioned

- dates from a date-table

so i got a new table with the sold articles and all days of a year without a salesamount:

Simulation =

SELECTCOLUMNS(

CROSSJOIN(

DISTINCT('Sales'[Artikel]),

CALENDAR(DATE(YEAR(MIN('Date-Table'[Date])), 1, 1), DATE(YEAR(MAX('Date-Table'[Date])), 12, 31))),

"SKUs", 'Sales)'[Artikel],

"Datum", [Date]

)

Now i need a third column in which i will put a calculation. Therefor i want to divide the salesamount (1. Table) of one month by the days of that month and put that distribution in each day of the month in a new column of the 2. Table.

Thank for helping me with this problem

Re: Easy CALCULATED TABLE - Separate Monthly Sales into Day Amount

johnt75 — Wed, 21 Jun 2023 10:08:15 GMT

You could build the table with

Daily Table = SELECTCOLUMNS ( GENERATE ( 'Sales', VAR StartDate = 'Sales'[Year month] VAR EndDate = EOMONTH ( 'Sales'[Year month], 0 ) VAR DatesInMonth = CALENDAR ( StartDate, EndDate ) VAR NumDays = COUNTROWS ( DatesInMonth ) VAR DailyAmount = DIVIDE ( 'Sales'[Sales amount], NumDays ) RETURN ADDCOLUMNS ( DatesInMonth, "Daily Amount", DailyAmount ) ), "SKUs", 'Sales'[Artikel], "Datum", [Date], "Daily Amount", [Daily Amount] )

This assumes that your year month column is actually a date type giving the first of the month. If its in a different format you would need to tweak the StartDate and EndDate variables accordingly.

Re: Easy CALCULATED TABLE - Separate Monthly Sales into Day Amount

devanshi — Wed, 21 Jun 2023 10:26:31 GMT

VAR Currmonth = 'Tablename2'[Month]
VAR Days = CALCULATE(COUNTROWS('Tablename2'), 'Tablename2'[Month] = Currmonth)
VAR currsales = SUMX( FILTER('TableName1' ,'TableName1'[Month] = Currmonth ), [SaleAmount])
RETURN
currsales / Days

Re: Easy CALCULATED TABLE - Separate Monthly Sales into Day Amount

Power-CJ — Wed, 21 Jun 2023 12:03:33 GMT

hello

thank you for the fast reply.

But i cant use this code.

The Variable currmonth i cant declare like this

how can i use countrows on tablename2 in the variable days if this table is not yet generated.

or i didnt understand the logic of your code?

Where i hava to put this in my existing code of the calculated table 'Simulation'?

Re: Easy CALCULATED TABLE - Separate Monthly Sales into Day Amount

Power-CJ — Wed, 21 Jun 2023 12:34:35 GMT

Genious, this look good and simple after recording. thx

but pbi throws an error for ur code:

"The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value."

Re: Easy CALCULATED TABLE - Separate Monthly Sales into Day Amount

johnt75 — Wed, 21 Jun 2023 12:50:14 GMT

Can you post the code you are using ?

Re: Easy CALCULATED TABLE - Separate Monthly Sales into Day Amount

Power-CJ — Wed, 21 Jun 2023 13:30:36 GMT

i found the mistake or the fixing

i declare the variable like following:

VAR _EndDate =

EOMONTH(_StartDate, 0)

now it works