Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I have 2 columns, one containing serial numbers of machines and one containing a date like a time stamp, when the machine was edited. I want to sum up the count of machines each day.
eg:
Serial no. | Date
S1 | 2/13/2020
S2 | 2/13/2020
S3 | 2/14/2020
S4 | 2/15/2020
S5 | 2/15/2020
->
Date | Count | cumulated values
2/13/2020 | 2 | 2
2/14/2020 | 1 | 3
2/15/2020 | 2 | 5
by using a quick measure it cumulates the values, but not by each day ascending but somehow randomly.
Thank you very much!
Solved! Go to Solution.
You can do it with help from date table. Now if you need cumulative to reset on month, qtr or year. use date* or total* functions. ie datesmtd, totalmtd
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
You can do it with help from date table. Now if you need cumulative to reset on month, qtr or year. use date* or total* functions. ie datesmtd, totalmtd
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(date,date[date] <=maxx(date,date[date])))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date])))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
YOu need to create two measure to accomplish you requirement:
Please note that you need to have a date table in the model, and a link between the date column in the date table and the date column in the fact table. The Date[date] column mentioned is the one in the date table of course, and the Table[Date] table mentioned is the one in the fact table
CALCULATE(
COUNT(
Table[Date],
),
FILTER(
ALL(
Date
),
Date[Date] <= MAX(Date[Date])
)
)
User | Count |
---|---|
94 | |
83 | |
78 | |
75 | |
66 |
User | Count |
---|---|
115 | |
105 | |
93 | |
65 | |
60 |