DAX calculation

[arif_tsrm]

From a single Fact table I have created two measures from two different column , one is "Total Staff Attendance"=Distinctcount(Table1[Name&Date]),  another measure is "NewDC"=Average(Table1[TotalDesk]) , please see the attached screenshort, I want to show in a monthly line chart how many days in a month the "Total Staff Attendance" is greater then "NewDC", the result will be 2 for February ( i.e. 7 , 9th February) 

[Ashish_Mathur]

Hi,

Assuming the Dates on the X-axis have been dragged from the Calendar Table, write this measure

=countrows(filter(values(calendar[Date]),[Total staff atendance]>[NewDC]))

Hoep this helps.

[arif_tsrm]

Thank you for your reply, dates on the X-axis have been dragged from the fact table( calender table have no date column , only month column is availavle)

[Ashish_Mathur]

That is not how it should be.  In the Calendar Table, there should always be a Date column.  All Date fields such as year, month etc. should be created in this Calendar Table and to all visuals, date related fields should be dragged from the Calendar Table.

[arif_tsrm]

thank you for your information, but in my data model( snowflake model) have a dimension table with monthly data , please see my data model

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[arif_tsrm]

it is fully confidential data, so without shearing have others option please?

[sevenhills]

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]

[arif_tsrm]

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]

[sevenhills]

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))

[arif_tsrm]

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-scr

return tst