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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to create Measure which calculates values for time period (first date of the month - 100 days) ?

Hi,

 

I am working on a dashbord for repeated order rate (ROR) for time period. At first, the time period is form the first date of the month - 100 days, and I need to display these values for time period (every month's first date - 100 days). 

I've been google and looking a lot aand nothing seems to work for me.

 

I need to calculate a some values - total order count, total unique customer count, customer count with single order, customer count with multiple orders.

 

For dates, I used separate Dates table, where I calculated first date for every month (column name is 

_First_date_of_the_month).
Daavis_0-1665380976015.png

 

The I have Orders Table, which contains  - Order Date, Order ID, Customer ID

I am try to create measures like this :

* Total Order count = 

   VAR _last_X_days = DATESINPERIOD('Dates'[_First_date_of_the_month],MIN('Dates'[_First_date_of_the_month]), -100, DAY)
VAR _orders_last_X_days =
CALCULATETABLE(
SUMMARIZE('Orders','Orders'[Order_ID]),
_last_X_days
)

VAR _order_count_last_X_days =
COUNTROWS(_orders_last_X_days)

RETURN
_order_count_last_X_days

 

 

* Total Customers = 

VAR _last_X_days =
DATESINPERIOD('Dates'[_First_date_of_the_month], MIN('Dates'[_First_date_of_the_month]), -100, DAY)

VAR _customers_last_X_days =
CALCULATETABLE(
SUMMARIZE('Orders','Orders'[Customer ID]),
_last_X_days
)

VAR _customers_count_last_X_days =
COUNTROWS(_customers_last_X_days)

RETURN
_customers_count_last_X_days

 

* Customers with single order = 

COUNTROWS(
FILTER(
SUMMARIZE('Order','Orders'[Customer ID],
"Total Purchases", [Total Order count] ),
[Total Order count] == 1
))
 
* Customer with multiple orders = 
COUNTROWS(
FILTER(
SUMMARIZE('Orders','Orders'[Customer ID],
"Total Purchases", [Total Order count] ),
[Total Order count] >=2))

 

 

The Table I need fill up, looks lsomething like this:

Daavis_1-1665382154447.png

 

Any suggestion to help me solve this problem would be greatly appreciated.

 

Some Notes:

* When I created Mesures, which calculated same valus, but only for each month, the formulas worked fine, but when I tryed to adjust them for Date Period, something is noth working.

11 REPLIES 11
johnt75
Super User
Super User

Try replacing your DATESINPERIOD calls with

DATESINPERIOD('Dates'[Date],MIN('Dates'[_First_date_of_the_month]), -100, DAY)
Anonymous
Not applicable

Thanks for the suggestion, but this is still not working for me. 

I replaced DATESINPERIOD calls, but it seems that now the values are calculated only for the first date of the month, and not for the entire period... 😞

 

 

you could make sure that the dates in period is returning the correct dates by amending the measure to return a countrows of the last_x_days variable. that might help to identify where the problem is

Anonymous
Not applicable

I checked - countrows of the measure last_x_days is returning 100.

and the "start date" and "end date" is matching for each period.... soooo it looks like the problem is with calculations for Order count.

which columns form the relationship from your Date table to the Orders table? 

Is the Date table marked as a date table?

What other filters are on your visual? Which other columns are in the visual?

Anonymous
Not applicable

Thank you for the recommendation to chechk if Date table is market as date table.

Seems like that was part of the problem, and now at least, "Total order count" and "Total customer" are calculated correctly, but "Customer with single order" and "Customer with mulltiple orders" are calculated incorectly - both solutins (the one I wsa using, and the one you suggested) are returning identical results.

 

I have thought and searched a lot - is there some multi-step solutinons (multiple measures) which could solve these issues?

You can use DAX Studio to test what is being returned for a sample date by running

DEFINE
    VAR DateFilter =
        TREATAS ( { DATE ( 2022, 9, 1 ) }, 'Date'[Date] )

EVALUATE
CALCULATETABLE (
    ADDCOLUMNS (
        VALUES ( 'Orders'[Customer ID] ),
        "@Total orders", [Total Order count]
    ),
    DateFilter
)

that might give an idea as to whether the total order count measure is returning expected results

Anonymous
Not applicable

I test the code taht you provided.

The returned table conatains two columns - Customer id and @Total order. The Customer ID values are those Customer, who has made order in given date (2022,9,1 - in this case), and in the @Total orders columns are number of orders in last 100 days for each of those Customers who has mada a order in 2022,9,1.

Anonymous
Not applicable

The relationship is between "Date" from Dates table and "Order date" from Orders table.

There are no filter to be simpler.

And in the visual, I am using "_First_date_of_the_month" as first column (for first dates of the month), and then comes "Order count", "Total Customer", "Customer count with single order" and "Customer count with mulltiple orders".

 

 

And I will chech if Dates table is marked as date table. Thanks for the suggestion.

 

 

This is probably not causing the problem but I noticed that you are using SUMMARIZE to add a calculated column, which is not a good idea. Normally you should use SUMMARIZE to only return the static values and then wrap that in an ADDCOLUMNS to perform the calculations, there's a SQLBI article about it.

However in this case you don't really need to add the column at all, you could rewrite it like

* Customers with single order =
COUNTROWS (
    FILTER ( VALUES ( 'Orders'[Customer ID] ), [Total Order count] = 1 )
)

Maybe this is not causing the problem at the moment, but its something to bear in mind.

Anonymous
Not applicable

Thank you,

I will bear in mind your recommendation.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors