Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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
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
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
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
9 | |
7 | |
7 | |
6 |
User | Count |
---|---|
21 | |
11 | |
10 | |
9 | |
8 |