Total not visible in Table

[nikkr38]

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

[v-yangliu-msft]

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]

Hey @v-yangliu-msft 

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