Error with Lookuptable "A table of multiple values are supplied"

[Krisvenkata11]

I am trying to prorate the data based on the current month data(cut off date is April 14). I need to take cutoff value which is on April14th and prorate it to all days in the month of May

I am getting a error "A table of multiple values are supplied" while executing below

LatestColumn = LASTDATE(SUMMARIZE(FILTER('Sheet1 (2)', 'Sheet1 (2)'[Work Item] <> 0),'Sheet1 (2)'[Date]))

----------------------------------------

Findval=

var k = FIRSTNONBLANK('Sheet1 (2)'[LatestColumn], 1)

Var X= LOOKUPVALUE('Table 1 (Sheet1)'[Sales], 'Table 1 (Sheet1)'[Date], k) 

my current input is:

Date	Sales
4/1/2022	10
4/2/2022	5
4/3/2022	4
4/4/2022	6
4/5/2022	3
4/6/2022	6
4/7/2022	5
4/8/2022	22
4/8/2022	22
4/9/2022	20
4/10/2022	22
4/11/2022	24
4/12/2022	23
4/13/2022	25
4/14/2022	20
4/14/2022	0
4/15/2022	0
4/16/2022	0
4/17/2022	0
4/18/2022	0
4/19/2022	0
4/20/2022	0
4/21/2022	0
4/22/2022	0
4/23/2022	0
4/24/2022	0
4/25/2022	0
4/26/2022	0
4/27/2022	0
4/28/2022	0
4/29/2022	0
4/30/2022	0
5/1/2022	0
5/2/2022	0
5/3/2022	0
5/4/2022	0
5/5/2022	0
5/6/2022	0
5/7/2022	0
5/8/2022	0
5/9/2022	0

  and expected output ise

Date	Sales
4/1/2022	10
4/2/2022	5
4/3/2022	4
4/4/2022	6
4/5/2022	3
4/6/2022	6
4/7/2022	5
4/8/2022	22
4/8/2022	22
4/9/2022	20
4/10/2022	22
4/11/2022	24
4/12/2022	23
4/13/2022	25
4/14/2022	20
4/14/2022	0
4/15/2022	0
4/16/2022	0
4/17/2022	0
4/18/2022	0
4/19/2022	0
4/20/2022	0
4/21/2022	0
4/22/2022	0
4/23/2022	0
4/24/2022	0
4/25/2022	0
4/26/2022	0
4/27/2022	0
4/28/2022	0
4/29/2022	0
4/30/2022	0
5/1/2022	20
5/2/2022	20
5/3/2022	20
5/4/2022	20
5/5/2022	20
5/6/2022	20
5/7/2022	20
5/8/2022	20

[tamerj1 · Accepted solution]

@Krisvenkata11 
Following to our messages, please refer to sample file with the updated solution https://www.dropbox.com/t/ChEaKYhinqk5wlVK

The number of working days shall be within a separate dimension table as shown in the pictures. I believe this is an easy job however please let me know if you need any help on that. 
Then adjust your model as shown in the screenshot and update the code as follows

Sales Amount = 
VAR CurretDate =
    MAX ( 'Date'[Date] )
VAR CurrentWeekDay =
    WEEKDAY ( CurretDate )
VAR WorkingDays =
    SELECTEDVALUE ( 'Working Days'[Cus_Bus_Days] )
VAR LastDateWithSales =
    CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS ( 'Date' ) )
VAR CurrentSales =
    SUM ( Sales[Sales] )
VAR LastDateSales =
    CALCULATE (
        SUM ( Sales[Sales] ),
        Sales[Date] = LastDateWithSales,
        REMOVEFILTERS ( 'Date' )
    )
VAR Result =
    SUMX (
        VALUES ( 'Date'[Date] ),
        CALCULATE (
            IF (
                CurretDate > LastDateWithSales,
                IF ( 
                    CurretDate <= EOMONTH ( LastDateWithSales, 0 )
                        || DIVIDE ( CurrentWeekDay * 6, WorkingDays ) = 1, 
                    0, 
                    LastDateSales 
                ),
                CurrentSales
            )
        )
    )
RETURN
    Result

I hope this answers your query 

[tamerj1]

@Krisvenkata11 
Following to our messages, please refer to sample file with the updated solution https://www.dropbox.com/t/ChEaKYhinqk5wlVK

The number of working days shall be within a separate dimension table as shown in the pictures. I believe this is an easy job however please let me know if you need any help on that. 
Then adjust your model as shown in the screenshot and update the code as follows

Sales Amount = 
VAR CurretDate =
    MAX ( 'Date'[Date] )
VAR CurrentWeekDay =
    WEEKDAY ( CurretDate )
VAR WorkingDays =
    SELECTEDVALUE ( 'Working Days'[Cus_Bus_Days] )
VAR LastDateWithSales =
    CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS ( 'Date' ) )
VAR CurrentSales =
    SUM ( Sales[Sales] )
VAR LastDateSales =
    CALCULATE (
        SUM ( Sales[Sales] ),
        Sales[Date] = LastDateWithSales,
        REMOVEFILTERS ( 'Date' )
    )
VAR Result =
    SUMX (
        VALUES ( 'Date'[Date] ),
        CALCULATE (
            IF (
                CurretDate > LastDateWithSales,
                IF ( 
                    CurretDate <= EOMONTH ( LastDateWithSales, 0 )
                        || DIVIDE ( CurrentWeekDay * 6, WorkingDays ) = 1, 
                    0, 
                    LastDateSales 
                ),
                CurrentSales
            )
        )
    )
RETURN
    Result

I hope this answers your query 

[tamerj1]

@Krisvenkata11 
Here is a sample file with the solution 

You cannot add rows to an existing table in power bi but you can add columns. What you call date table can actually be your new sales table. This is one option. Other option is to rely on a table visual and utilize measures.

• Option 1

https://www.dropbox.com/t/IwoZ27YoMQBMbuyQ

Here I suppose you have only the Sales table. Then we can start creating a new sales table that contains all the dates up to the end of the year. 

All Dates Sales = CALENDAR ( MIN ( Sales[Date] ), DATE ( YEAR ( MAX ( Sales[Date] ) ), 12, 31 ) )

Then create one to many relationship with the original sales table

Now you can add a new column in the new table to retrieve sales for respective dates and last date sales for future dates. Here I prefer to use SUMX in order to aggregated all the sales of a day in case you have multiple sales in on day

Sales = 
VAR CurrentDateSales =
    SUMX ( RELATEDTABLE ( Sales ), Sales[Sales] )
VAR TableWithSales =
    FILTER ( Sales, Sales[Sales] > 0 )
VAR LastDateWithSales =
    MAXX ( TableWithSales, Sales[Date] )
VAR LastDateSales =
    SUMX (
        FILTER ( TableWithSales, Sales[Date] = LastDateWithSales ),
        Sales[Sales]
    )
RETURN
    IF (
        'All Dates Sales'[Date] > LastDateWithSales,
        LastDateSales,
        CurrentDateSales
    )

• Option 2

https://www.dropbox.com/t/j5GqpHeLUJqydJpV

Create The Date table using the same code as above. Then creating one to many relationship between the two tables. Then write a Measure 

Sales Amount = 
VAR CurretDate =
    MAX ( 'Date'[Date] )
VAR LastDateWithSales =
    CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS ( 'Date' ) )
VAR CurrentSales =
    SUM ( Sales[Sales] )
VAR LastDateSales = 
    CALCULATE ( SUM ( Sales[Sales] ), Sales[Date] = LastDateWithSales, REMOVEFILTERS ( 'Date' ) )
RETURN
    SUMX ( 
        VALUES ( 'Date'[Date] ),
        CALCULATE ( 
            IF ( 
                CurretDate > LastDateWithSales,
                LastDateSales,
                CurrentSales
            )
        )
    )

In a table visual place the Date[Date] (Do not summarize) and then place the measure

Please let me know if you have any doubt or any question.

[tamerj1]

Hi @Krisvenkata11 

Do you actually have the date Apr. 14th twice? Are you trying to create a neasure or a calculated column?

[Krisvenkata11]

Yes, i have the Apr 14 data twice. I am creating a column.

Apr 14  - 20

Aprl 14 - 0

Single value works fine. My challenge how to handle above?

[tamerj1]

@Krisvenkata11 

You can achieve than in many ways. One way to start with retrieving the last date with sales

VAR TableWithSales =
FILTER ( Table, Table[Sales] > 0 )

VAR LastDateWithSales = 

MAXX ( TableWithSales, Table[Date] )

then retrive the sales value

VAR LastDateSales =
MAXX ( FILTER ( TableWithSales, Table[Date]  = LastDateWithSales ), Table[Sales] )

then RETURN

IF ( Table[Sales] = 0, LastDateSales, Table[Sales] )

[Krisvenkata11]

Thanks a ton, this will work if i have everything in one table but my issue is:

I have a table called 'DatesAll' which contains all the dates from Jan1 st to Dec 31st 2022

I am joining it with sales table which is having the DateSale Column till April 14th(cut off Date)

When i am using Maxx function it is not allowing me to refer the Date column in DatesAll table and only showing the Measures of DatesAll table not columns. Is cross table reference not allowed in Maxx function although both are joined?

[tamerj1]

@Krisvenkata11 

Can you please share some screenshots?

[Krisvenkata11]

Sales Table Data

Date Table( from Jan 1st to 31st Dec)

Below is not taking column  from DateTable and expecting only SalesTables Columns

VAR LastDateWithSales = MAXX ( TableWithSales, 'SalesTable'[Date] )

[tamerj1]

Hi @Krisvenkata11 

here is the final solution based on option 2

Sales Amount =
VAR CurretDate =
    MAX ( 'Date'[Date] )
VAR LastDateWithSales =
    CALCULATE ( MAX ( Sales[Date] ), REMOVEFILTERS ( 'Date' ) )
VAR CurrentSales =
    SUM ( Sales[Sales] )
VAR LastDateSales =
    CALCULATE (
        SUM ( Sales[Sales] ),
        Sales[Date] = LastDateWithSales,
        REMOVEFILTERS ( 'Date' )
    )
RETURN
    SUMX (
        VALUES ( 'Date'[Date] ),
        CALCULATE (
            IF (
                CurretDate > LastDateWithSales,
                IF ( CurretDate <= EOMONTH ( LastDateWithSales, 0 ), 0, LastDateSales ),
                CurrentSales
            )
        )
    )