Forum Discussion

nikkr38's avatar
nikkr38
New Member
2 years ago

Total not visible in Table

Hi everyone, 

I have a issue, where values are shown on row level but not at the total level.

Logic for the calculation has to be as followed:
1. We have to check Billings for the previous month when the Net Billings was not 0, then I have to sum Cost after that period when Net Billings is not 0.
2. If there no record where the Net Billings is not 0 then sum all the Cost. 

Cost = 

 

    CALCULATE(
        SUM('Project Actuals'[Internal_Costs]),
        USERELATIONSHIP('Project Actuals'[Date],'Rev Rec'[Date]))​<p> </p><p> </p><p> </p><p> <li-code lang="markup">Billings = SUM('Rev Rec'[Net Billings])

 


Below is the DAX which I have tried, but it's not giving me total.

 

 

Final Cost = 

VAR _currentmonth = SELECTEDVALUE('Rev Rec'[Date])
VAR _lastNonZeroDate = 
    CALCULATE(
        LASTDATE('Rev Rec'[Date]),
        FILTER(
            ALL('Rev Rec'[Date]),
            'Rev Rec'[Date] < _currentmonth && [Net Billings] <> 0
        )
    )
VAR _earliestDate = 
    CALCULATE(
        MIN('Rev Rec'[Date]),
        FILTER(
            ALL('Rev Rec'[Date]),
            [Net Billings] = 0
        )
    )
RETURN 

    IF(
        ISBLANK(_lastNonZeroDate), 
        CALCULATE(
            [Internal Cost], 
            'Rev Rec'[Date] >= _earliestDate && 'Rev Rec'[Date] < _currentmonth),
            IF(
                _lastNonZeroDate <> BLANK(), 

                CALCULATE(
                    [Internal Cost],
                    FILTER(ALL('Rev Rec'[Date]),
                    'Rev Rec'[Date] > _lastNonZeroDate && 'Rev Rec'[Date] < _currentmonth))Below image shows, the first scenario when we have a Value in Net Billings, so in this case I need sum after that period.
 

Second Scenario, when there is no values for previous months in Net Billings, then we have to sum everything in Cost.

I would appreciate your help, and if there are any better solution for this.

Thanks,
Nik

"DAX"
"Power BI"
"Totals"
Last date

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi  nikkr38 ,

     

    Here are the steps you can follow：

    1. Create measure.

    Measure =
var _maxdate=
MAXX(
    ALLSELECTED('Table'),'Table'[Date])
var _value=
SUMX(
    FILTER(ALLSELECTED('Table'),'Table'[Date]=_maxdate),[Net Billings])
RETURN
IF(
    _value<>0&&HASONEFILTER('Table'[Date]),SUM('Table'[Internal Cost]),
    SUMX(
       FILTER( ALLSELECTED('Table'),[Net Billings]<>0),[Internal Cost]))

    2. Result:

     

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

    • nikkr38's avatar
      nikkr38
      New Member
      2 years ago

      Hey Anonymous 

      I appreciate your efforts in solving this problem, but the soltuion is not correct.
      I need only the sum of cost, after the most recent date when there was a value in Billings & if there is no value in billings for previous month then I need sum of all the internal cost.

      FYI : Date has to be most recent when there was a value in Billing for previous months.

      Let me know if you need, more info.

      Thanks,
      Nik