Easy CALCULATED TABLE - Separate Monthly Sales into Day Amount

[Power-CJ]

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

[johnt75 · Accepted solution]

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.

[devanshi]

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

[Power-CJ]

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'?

[johnt75 · Accepted solution]

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.

[Power-CJ]

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."

[johnt75]

Can you post the code you are using ?

[Power-CJ · Accepted solution]

i found the mistake or the fixing

i declare the variable like following:

VAR _EndDate =

            EOMONTH(_StartDate, 0)

now it works