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 get accumulated number of transactions over a given period

I have been able to visualize the number of transactions on some machines on a day-to-day level, shown in the example below:

transactions.png

 

What I want to be able to do is to get these transactions on an aggregated/accumulated level over the same period (april to july). The data behind it looks somewhat similar to this:

 

SessionMachine IDTimestampCustomer ID
1000XX999XX03.04.20888XX
1000XX999XX05.04.20888XX
1000XX999XX17.05.20888XX
1000XX999XX19.05.20888XX
1000XX999XX19.05.20888XX

 

The question is if I should create another column in my data that calculates the accumulated number of transactions or if I should create a measure.

8 REPLIES 8
Thejeswar
Resident Rockstar
Resident Rockstar

@Anonymous ,

I am not very clear as to what your need here is..

 

What I understand is that you want each day to day values to add up with one another such that for example, 

30-Jun data should have sum of all data from 1-Apr to 30-Jun

nooftrans = CALCULATE(COUNT(system[Timestamp]), FILTER(ALL(system), system[Timestamp] <= MAX(system[Timestamp])))

If my understanding is right on what you need, may be you can use the below dax to create a measure and achieve this.

Don't forget to group you dates by quarter while seeing the data

 

If this is not what you need, pls. give some more details to your question

Regards,

 

Anonymous
Not applicable

I probably explained a bit to easy earlier. I will try to explain in an example:

 

If I filter a machine in my report and it has had 47 transactions in for example the period 02.01.17-02.04.17. During this period some transactions happened on the same date. I want to make a measure/diagram that accumulates like this: 

 

accumulation.png

In the example above there has been two transactions 02.01, three transactions 05.01 and four transactions 07.01. As you can see the chart accumulates as the transactions happens. In the original post it just shows the day-by-day transactions. Let me know if you need more information @Thejeswar . 

@Anonymous ,

Using that measure in a line chart will give the below output

Thejeswar_0-1594198802475.png

If it is that you are trying to draw a line based on the distinct transactions that happen on each day unlike the above case, just add Date to your x axis and count of date o the Y axis, that will give a chart like the one below

Thejeswar_1-1594198913920.png

Keeping X axis labels as categorical, which by defaut is continuous, will give the dates separately below

 

Hope this helps!!

Regards,

 

Anonymous
Not applicable

I have tried your formula once again, but I did not manage to make it work. 

 

I probably need to explain it a bit deeper. What I am trying to show is the number of transactions from first operation date and for a given period from this date, I have in some way been able to do that. This probably means that I should use some other input in the measure. 

 

The relationships between my data is one dataset including machine-id and first operation date and one dataset including all transactions with timestamp, machine-id etc included per row in the data. The tables are related using the machine ID. 

 

I already have a measure counting all transactions and a measure counting all transactions 90 days going forward from operation date:

Total transactions = COUNT(factTransaction[session])

Operation date +90 = CALCULATE([Total transactions],DATESINPERIOD(Datotabell[Date],FIRSTDATE(dimNode[operation_date]),90,DAY))

 

As you know the problem occurs when I try to put the operation date 90+ measure in a line chart and it shows the daily transactions with no accumulation @Thejeswar. Thanks for the help so far!

Hi @Anonymous ,

The Measure that you are using will generate a single value irrespective of Machine ID or Timestamp and hence when plotted in a line chart will give a single straight line

 

Also Do you mean that you want to get for every date, the number of transactions in the next 90 days?

You may use the below measure for this purpose

 

 

measure = 
var a = MIN(factTransaction[Timestamp])
return
CALCULATE(SUM(factTransaction[transactions]), DATESBETWEEN(factTransaction[Timestamp], a, a + 90))

where transactions (is a column) = count(factTransaction[session])

 

 

or

 

 

measure = CALCULATE([Total transactions],DATESINPERIOD(factTransaction[Timestamp],FIRSTDATE(factTransaction[Timestamp]),90,DAY))

Total Transactions = COUNT(factTransaction[session])

 

 

This  measure gives for every date, the number of transaction for 90 days from it.

The Below is the screenshot

 

Thejeswar_0-1594214964450.png

 

FYR..

The Sample data I used

Thejeswar_0-1594215061609.png

 

Regards,

 

lbendlin
Super User
Super User

If you don't allow your users to interact with the report/dashboard then a calculated column is enough. If you expect them to change filters (date range, other criteriae) then you need to use a measure.

Anonymous
Not applicable

Ok, I would like to be able to change filters, so a measure is optimal. Do you have an idea regarding how I should do it @lbendlin ? 

Look at the "Quick Measure"  option when you right click the table. It has some ready made patterns.

 

Also check out https://daxpatterns.com - lots of good examples.

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