Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
FredLEGUEN
Helper III
Helper III

Calculated column for the sales of the month to create Custom Data Type

Hi,

 

I don't know if you know that Excel can display the contained of a Data Model table. The result is quiet interesting because you can use the Custom Data Type to associate your tables directly in a worksheet. 

If you don't the principe, you specify in your data model a Table with a column ID and in Excel, you can call each one of the columns of that table like in this example

Example of Custom Data Type.png

 

As you can see, I was able to create a calculated column for the last purchase date with this measure

 

LastDateAchat = MAX(Ventes[Date_Commande])

 

 

And I used the previous measure for the second calculated column to return the amount of the last purchase

 

ValueLastPurchase = CALCULATE([CA],FILTER(Ventes,Ventes[Date_Commande]=Clients[LastDatePurchase]))

 

 

But, I'm not able to find the calculation to return the amount of sales for the previous month

 

SalesLast_Month = 
VAR Last_Month = DATE(YEAR(TODAY()),MONTH(TODAY())-1, 1)
RETURN IF (Clients[LastDatePurchase] >= Last_Month, 
           CALCULATE(
               [CA], 
                    FILTER(
                    Ventes,
                    Ventes[Date_Commande]>=DATE(YEAR(Clients[LastDatePurchase]), MONTH(Clients[LastDatePurchase])-1, 1))),
           BLANK())

 

 

I know that wiht a measure, it's a piece of cake but with this calculation, the result isn't the one expected

Calculated columns.png

 

And the data model is a basic one: Clients, Products and Sales (Ventes)

Data Model.png

 

Any idea to return the sales for each Client ID with a calculated column?

Thanks

1 ACCEPTED SOLUTION

Hi @selimovd ,

I have tried your solution but it doesn't solve the problem.

 

But I found a way to solve this with Power Query

I have created a temporary table with only the sales of the previous month and apply a group by.

And finally, I have merge the Client Table with this temparory tables.

It works, but I would prefer to do the job with a measure

View solution in original post

2 REPLIES 2
selimovd
Super User
Super User

Bonjour @FredLEGUEN ,

 

probably I found the mistake.

When you're working in a calculated column you should be aware of the context transition (when is value from the row and when from the whole table). The moment you use CALCULATE the context transition happens from the row to the filter context.

 

In your case this means you can't access the LastDatePurchase in the row anymore, so you have to save it before as a variable before the transition happens.

 

Try the following measure:

SalesLast_Month =
VAR Last_Month = DATE( YEAR( TODAY() ), MONTH( TODAY() ) - 1, 1 )
VAR vLastDatePurchase = Clients[LastDatePurchase]
RETURN
    IF(
        Clients[LastDatePurchase] >= Last_Month,
        CALCULATE(
            [CA],
            FILTER(
                Ventes,
                Ventes[Date_Commande] >= DATE( YEAR( vLastDatePurchase ), MONTH( vLastDatePurchase ) - 1, 1 )
            )
        ),
        BLANK()
    )

 

If you need any help please let me know.
If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍
 
Best regards
Denis
 

Hi @selimovd ,

I have tried your solution but it doesn't solve the problem.

 

But I found a way to solve this with Power Query

I have created a temporary table with only the sales of the previous month and apply a group by.

And finally, I have merge the Client Table with this temparory tables.

It works, but I would prefer to do the job with a measure

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.