Forum Discussion
DAX calculation
can you clarify further?
(a) Calculate Total Staff Attendance as distinct count for a day and sum of each day at month level
(or) distinct count for a day ; for a month
(b) Calculate NewDC as average for a day and sum of each day at month level
(or) average for a day ; for a month
(c) Diff of (a) - (b) at day level and at month level
[a] - [b]
Formula varies based on your needs!
Some links to tell what I am asking ...
https://www.youtube.com/watch?v=UcJ-pgPp5Bo
https://www.powertechtips.com/calculate-monthly-averages-power-bi/
Depends on what you like
Total Staff Attendance = SUMX( VALUES ( 'DimDate'[MonthName] ), Distinctcount(Table1[Name&Date]))
NewDC = AverageX ( Values( 'DimDate'[MonthName]) , Table1[TotalDesk])
Diff Calc = [Total Staff Attendance] - [NewDC]
Thank you for your reply, I have used your measure and get the difference just I need to know how many days in a month the difference is positive( it is in monthly, the result will 2 days in February)
(a) Calculate Total Staff Attendance as distinct count for a day ; for a month
(b) Calculate NewDC as average for a day ; for a month
(c) Diff of (a) - (b) at month level
[a] - [b]
Can you share your full DAX syntax? (instead of picture)
Try this and see if it helps
Positive Diff Calc Count = SUMX( VALUES ( 'DimDate'[MonthName] ), If( [Diff Calc] >= 0, 1, 0))please see the measure
Diff = VAR ST=SUMX(VALUES(DimCalender[Month Name]),DISTINCTCOUNT(FactAccessControl[Name&Date]))var scr=AVERAGEX(VALUES(DimCalender[Month Name]),AVERAGE(FactAccessControl[TotalDeskGrp.TotalDesk]))var tst=ST-scrreturn tst