Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a matrix table that I have to display the Year , Month and Date in rows and the counts of unique accounts per day as column. To this, i need to add a new column to indicate the up/downswing from previous "calendar". Meaning, for a day, i need to display the up/down swing from previous day. For month, the unique count in that month with the up/down swing from previous month and similar for year.
I am able to get the values as per below DAX for the days, but the months and years are incorrect, as it seems to be summing up the days, rather than the unique account for that month. And subsequently need to do this when the data increases by year.
AccessDate | AcctID |
9/1/2020 | A1001 |
9/1/2020 | B1001 |
9/1/2020 | C1001 |
9/1/2020 | D1001 |
9/1/2020 | E1001 |
9/2/2020 | A1001 |
9/2/2020 | B1001 |
9/2/2020 | C1001 |
9/2/2020 | D1001 |
9/2/2020 | E1001 |
9/2/2020 | F1001 |
9/3/2020 | B1001 |
9/4/2020 | C1001 |
9/4/2020 | D1001 |
9/5/2020 | C1001 |
9/5/2020 | D1001 |
9/5/2020 | E1001 |
9/5/2020 | F1001 |
9/5/2020 | G1001 |
8/1/2020 | A1001 |
8/1/2020 | B1001 |
8/1/2020 | C1001 |
8/1/2020 | D1001 |
8/1/2020 | E1001 |
8/2/2020 | C1001 |
8/2/2020 | D1001 |
8/2/2020 | E1001 |
8/4/2020 | C1001 |
8/3/2020 | D1001 |
8/3/2020 | E1001 |
8/4/2020 | F1001 |
8/4/2020 | G1001 |
8/5/2020 | D1001 |
8/5/2020 | E1001 |
8/5/2020 | F1001 |
7/1/2020 | A1001 |
7/1/2020 | B1001 |
7/1/2020 | C1001 |
7/1/2020 | D1001 |
7/1/2020 | E1001 |
7/2/2020 | B1001 |
7/2/2020 | C1001 |
7/2/2020 | D1001 |
7/3/2020 | A1001 |
7/3/2020 | B1001 |
7/3/2020 | C1001 |
7/3/2020 | D1001 |
7/3/2020 | E1001 |
Intended Output for month and day is ..
AccessDate | AcctID Count | Previous Count swing |
Sep | 7 | 0 |
9/5/2020 | 5 | 3 |
9/4/2020 | 2 | 1 |
9/3/2020 | 1 | -5 |
9/2/2020 | 6 | 1 |
9/1/2020 | 5 | 2 |
Aug | 7 | 2 |
8/5/2020 | 3 | 0 |
8/4/2020 | 3 | 1 |
8/3/2020 | 2 | -1 |
8/2/2020 | 3 | -2 |
8/1/2020 | 5 | 2 |
Jul | 5 | 0 |
7/5/2020 | 3 | 2 |
7/4/2020 | 5 | 0 |
7/3/2020 | 5 | 2 |
7/2/2020 | 3 | -2 |
7/1/2020 | 5 | 0 |
So July had 5 unique accounts and Aug had 7 unique so the upswing of 2 for Aug. The individual days within July are correct as they are unique for that day and so on.
* I'm assuming only 5 days in a month for this example. 8/1/20 is looking at 7/5/20, and 9/1/20 is looking at 8/5. In full report, 9/1 will be looking at 8/31, and 8/1 will be looking at 7/31 etc for the previous value for the up/down swing.
The DAX i am using are.
AcctID Count = CALCULATE(DISTINCTCOUNT(Account[AcctID]),USERELATIONSHIP(Date[Date],Account[AccessDate])) |
AcctID Yesterday = CALCULATE(DISTINCTCOUNT(Account[AcctID]),DATEADD(Date[Date],-1,DAY),USERELATIONSHIP(Date[Date],Account[AccessdateDate])) |
Previous Count Swing = [AcctID Count]-[AcctID Yesterday]
|
Please let me know if my request id possible within the same matrix? Or do i have to seperate them into a year and Month and Daily visuals.
Solved! Go to Solution.
Hi @PBI5851 ,
How about this:
Previous Count swing =
VAR ThisMonth =
MONTH ( MAX ( 'Account'[AccessDate] ) )
VAR LastDayofPreviousMonth =
CALCULATE (
MAX ( 'Account'[AccessDate] ),
FILTER (
ALLSELECTED ( 'Account' ),
MONTH ( 'Account'[AccessDate] ) = ThisMonth - 1
)
)
VAR ValueofLastDayofPreviousMonth =
CALCULATE (
[AcctID Count],
FILTER (
ALLSELECTED ( 'Account' ),
'Account'[AccessDate] = LastDayofPreviousMonth
)
) + 0
VAR PreviousCount =
IF (
HASONEVALUE ( Account[AccessDate] ),
CALCULATE (
[AcctID Count],
FILTER (
ALL ( 'Account' ),
'Account'[AccessDate]
= MAX ( 'Account'[AccessDate] ) - 1
)
),
IF (
ThisMonth = MONTH ( MINX ( ALLSELECTED ( Account ), Account[AccessDate] ) ),
0,
CALCULATE (
[AcctID Count],
FILTER (
ALLSELECTED ( 'Account' ),
MONTH ( 'Account'[AccessDate] ) = ThisMonth - 1
)
)
)
)
RETURN
[AcctID Count]
- IF (
DAY ( MAX ( Account[AccessDate] ) ) = 1,
ValueofLastDayofPreviousMonth,
PreviousCount
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @PBI5851 ,
How about this:
Previous Count swing =
VAR ThisMonth =
MONTH ( MAX ( 'Account'[AccessDate] ) )
VAR LastDayofPreviousMonth =
CALCULATE (
MAX ( 'Account'[AccessDate] ),
FILTER (
ALLSELECTED ( 'Account' ),
MONTH ( 'Account'[AccessDate] ) = ThisMonth - 1
)
)
VAR ValueofLastDayofPreviousMonth =
CALCULATE (
[AcctID Count],
FILTER (
ALLSELECTED ( 'Account' ),
'Account'[AccessDate] = LastDayofPreviousMonth
)
) + 0
VAR PreviousCount =
IF (
HASONEVALUE ( Account[AccessDate] ),
CALCULATE (
[AcctID Count],
FILTER (
ALL ( 'Account' ),
'Account'[AccessDate]
= MAX ( 'Account'[AccessDate] ) - 1
)
),
IF (
ThisMonth = MONTH ( MINX ( ALLSELECTED ( Account ), Account[AccessDate] ) ),
0,
CALCULATE (
[AcctID Count],
FILTER (
ALLSELECTED ( 'Account' ),
MONTH ( 'Account'[AccessDate] ) = ThisMonth - 1
)
)
)
)
RETURN
[AcctID Count]
- IF (
DAY ( MAX ( Account[AccessDate] ) ) = 1,
ValueofLastDayofPreviousMonth,
PreviousCount
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@PBI5851 , You can use date table and time intelligence
examples
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))
This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))
/////month
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))
diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])
////Year
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
Power BI — Year on Year
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Mon th On Month
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...
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
Appreciate your Kudos.
@amitchandak . Thank you. I used your suggestion on the Day behind sales from another ticket. If i read it correctly, your solutions gives the correct value if looking at just year or month. But can all that be done in a single visual.
@PBI5851 , Are planning to switch these measures.
Then we have created a measure slicer.
I have done once combined it using is filtered for month and year - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
Last Period Employee, I switched the filter clause, the same thing for a year, month, and day
Other way is measure slicer: https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
I doubt there is one formula that can do. Unless you one year behind data, this will work with each grouping, day, month and year
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |