Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have been able to visualize the number of transactions on some machines on a day-to-day level, shown in the example below:
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:
Session | Machine ID | Timestamp | Customer ID |
1000XX | 999XX | 03.04.20 | 888XX |
1000XX | 999XX | 05.04.20 | 888XX |
1000XX | 999XX | 17.05.20 | 888XX |
1000XX | 999XX | 19.05.20 | 888XX |
1000XX | 999XX | 19.05.20 | 888XX |
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.
@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,
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:
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
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
Keeping X axis labels as categorical, which by defaut is continuous, will give the dates separately below
Hope this helps!!
Regards,
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
FYR..
The Sample data I used
Regards,
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.
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.
User | Count |
---|---|
42 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
54 | |
35 | |
18 | |
18 | |
15 |