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,
We are moving our SQL stored procedures into Power BI dax formulas. One of the stored proceudre function was:
Compare the todays count with yesterday count.
Total count can be a positive value or a negative value.
We send email of total count daily to the team.
Now we want to embed this logic into power BI so that team can see total count in power bi.
Current Table structure is
ID RegistrationNumber DateAdded
1 XYZ 01-06-2016
2 ABC 01-09-2016
3 GHI 15-01-2017
My question is that how can I achieve this logic in Power BI in which I can add calcualted columns that
stores today's count and yesterday's count value.
I tried to add calculated column but of no vein, can someone please help me to achieve this target.
Many Thanks
Jibran Ishtiaq
Solved! Go to Solution.
Please try with following DAX column formula if the result is what you desire.
TodayCount = VAR CurrentDate = CALCULATE ( MAX ( Table1[DateAdded] ) ) RETURN CALCULATE ( DISTINCTCOUNT ( Table1[RegistrationNumber] ), FILTER ( Table1, Table1[DateAdded] = CurrentDate ) )
YesterdayCount = VAR YesterdayDate = DATEADD ( Table1[DateAdded], -1, DAY ) RETURN CALCULATE ( DISTINCTCOUNT ( Table1[RegistrationNumber] ), FILTER ( Table1, Table1[DateAdded] = YesterdayDate ) )
Best Regards,
Herbert
Please try with following DAX column formula if the result is what you desire.
TodayCount = VAR CurrentDate = CALCULATE ( MAX ( Table1[DateAdded] ) ) RETURN CALCULATE ( DISTINCTCOUNT ( Table1[RegistrationNumber] ), FILTER ( Table1, Table1[DateAdded] = CurrentDate ) )
YesterdayCount = VAR YesterdayDate = DATEADD ( Table1[DateAdded], -1, DAY ) RETURN CALCULATE ( DISTINCTCOUNT ( Table1[RegistrationNumber] ), FILTER ( Table1, Table1[DateAdded] = YesterdayDate ) )
Best Regards,
Herbert
Hi Herbert,
Sorry to be naive on this, with your help I managed to have today counter column in my Power Bi table.
But I am struggling to find out that how can I dsiplay this on my Power BI dashboard.
Like I would like to display this as Today Count = 7.
Date Today Count
Many Thanks
Jibran Ishtiaq
Hi,
Thanks for your help it has worked but how can I display yesterday count in Power Bi Dashboard.
Do I have to use measure to bring today count of vehicles?
Many Thanks
Jibran Ishtiaq
I would create a date table and then simply do a custom column like this in your date table:
MyColumn = CALCULATE(COUNT([column]),RELATEDTABLE(Registrations))
This would give you a count for each date in your date table.
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 |
---|---|
113 | |
97 | |
79 | |
73 | |
56 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |