Calculate sales of previous day as a calculated column

[jsteffe]

Hello,

I get the following table named "ventes":

id_produit	id magasin	id_client	nb_articles	Date_vente	prix	CA
1	1	1	10	04/01/2019	5,83018261	58,3018261
1	1	2	15	04/01/2019	8,180047586	122,7007138
1	1	3	18	04/01/2019	10,70471882	192,6849388
1	3	1	14	05/01/2019	11,61213052	162,5698273
1	3	2	35	05/01/2019	4,747032468	166,1461364

I would like to add a calculated column with the amount osfsales (field "CA") for the previous day.

I created a table calendar with all the dates because it seems that DAX DATE functions require this table.

lI created a relationship between my tables ventes and calendar.

I created the following measure :

CALCULATE(SUM(ventes[CA]);PREVIOUSDAY('calendar'[Date]))

I can put in in a report but i want to put in as a calculated column in my table.

Any help would be appreciated.

Thanks,

Jérôme

[v-lili6-msft · Accepted solution]

hi  @jsteffe 

You could try this formula:

Column 1 = 
VAR _Yesterday =DATEADD ( 'calendar'[Date], -1, DAY )
RETURN
    CALCULATE ( SUM ( ventes[CA] ),
        FILTER (
            ventes,
                ventes[id_produit] = EARLIER ( ventes[id_produit] )
                && ventes[id_client] = EARLIER ( ventes[id_client] )
                && ventes[Date_vente] = _Yesterday
        )
    )

or

Column 2 =
VAR _Yesterday = DATEADD ( 'calendar'[Date], -1, DAY )
RETURN
    CALCULATE (  SUM ( ventes[CA] ),
        FILTER (
            ventes,
               ventes[id_produit] = EARLIER ( ventes[id_produit] )
                && ventes[Date_vente] = _Yesterday
        )
    )

Result:

If not your case, What is your expected output, could you please show the expected output in a table forus.

and here is sample pbix file, please try it.

Regards,

Lin

[Ashish_Mathur · Accepted solution]

Hi,

This calculated column formula works

=CALCULATE(SUM(Data[CA]),FILTER(Data,Data[Date_vente]=EARLIER(Data[Date_vente])-1))

Hope this helps.

[v-lili6-msft · Accepted solution]

hi  @jsteffe 

You could try this formula:

Column 1 = 
VAR _Yesterday =DATEADD ( 'calendar'[Date], -1, DAY )
RETURN
    CALCULATE ( SUM ( ventes[CA] ),
        FILTER (
            ventes,
                ventes[id_produit] = EARLIER ( ventes[id_produit] )
                && ventes[id_client] = EARLIER ( ventes[id_client] )
                && ventes[Date_vente] = _Yesterday
        )
    )

or

Column 2 =
VAR _Yesterday = DATEADD ( 'calendar'[Date], -1, DAY )
RETURN
    CALCULATE (  SUM ( ventes[CA] ),
        FILTER (
            ventes,
               ventes[id_produit] = EARLIER ( ventes[id_produit] )
                && ventes[Date_vente] = _Yesterday
        )
    )

Result:

If not your case, What is your expected output, could you please show the expected output in a table forus.

and here is sample pbix file, please try it.

Regards,

Lin

[jsteffe]

Thanks a lot for your solution.

I really appreciated your help ...

[Tahreem24]

@jsteffe ,

Try the below DAX approach:

Measure 3 = CALCULATE(Sum('Table[CA]),DATEADD('Table[Date_vente],-1,DAY))

Don't forget to give thumbs up 👍 and accept this as a solution if it helped you.

[jsteffe]

I created my measure as you wrote :

Measure 1 = CALCULATE(Sum(ventes[CA]);DATEADD(ventes[Date_vente];-1;DAY))

But How can I put it into a calculated coloumn ? Becaus I have to use again this result to do other calculations.

Thanks for your help ...

[Tahreem24]

@jsteffe ,

You can use this measure into new Custom fields without using any Aggregation function. Directly use the name of this measure in your new calculated column or measure.

Don't forget to give thumbs up 👍 and accept this as a solution if it helped you.

[jsteffe]

If I use this measure in my calculated column, I only get blank values.

Inside a report, this measure give the right values when I use it with the Date from table Calendar but give blank values if I use it with the SaleDate from table Sales ...

[amitchandak]

In a table, you can have when you have data at the day level. or based on some unique id. Looking at what you share you have use measure.