Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX Formula Help

I am trying to pull the scheduled hours for the previous period. For example, June Period 2 the scheduled hours is 36,638.77 and the last period should be 40,230.19 instead it's showing 345,622.21. Another example is June Period 1 the scheduled hours is 40,230.19 and the last period should be 35,244.43 instead it's showing 285,858.76.

 

I can't seem to figure out what the issue is with my DAX and why it's returning numbers significantly greater.

Scheduled Chg Booked Hours is also a formula by the way that takes the Sum of Booking Hours and filters for Charge Type: Chargeable and Booking Status: P.

 

Current DAX:

Sched Chg Booked Hrs Last Period =

var LastPeriod = MAX('dim FiscalFinancialPeriod'[PreviousPeriod])

var ReturnThis =

CALCULATE(SUM('fact BookedTime'[BookingHours])
, FILTER(ALL('dim Booking'), 'dim Booking'[Charge vs. Noncharge]="Chargeable")
, FILTER(ALL('fact BookedTime'),'fact BookedTime'[BookingStatus]="P")
, FILTER(ALL('dim FiscalFinancialPeriod'), 'dim FiscalFinancialPeriod'[FiscalFinancialPeriod]=LastPeriod)
)

return (ReturnThis)
Need Help

3 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    5 years ago

    You use lots of ALL() - that might be the issue. What are the filters on your visual?

  • Icey's avatar
    Icey
    Community Support
    5 years ago

    Hi Anonymous ,

     

    Please share us a dummy .pbix file for test. Please remove sensitive data and unnecessary visuals. It is suggested to upload your file to OneDrive for Business and then paste the link here.

     

     

    Best regards

    Icey