Forum Discussion
Resolver IITotal is wrong when categorised is vendor wise
santoshlearner2 Please check which MonthDate-field are you using in the report.
One common mistake is to have a DateDimTable, but use the Date-field from a transaction table.
Best practice: Use the DateDimTable Field at the report
Alternate solution if you are using the Table1[Month] field at the report:Sales Amount MTD := VAR LastVisibleDate = MAX ( 'Table1'[Month] ) VAR CurrentYear = YEAR ( LastVisibleDate ) VAR CurrentMonth = MONTH ( LastVisibleDate ) VAR SetOfDatesMTD = FILTER ( ALL ( 'Table1'[Month] ), 'Table1'[Month] <= LastVisibleDate && YEAR ( 'Table1'[Month] ) = CurrentYear && MONTH ( 'Table1'[Month] ) = CurrentMonth ) VAR Result = CALCULATE ( SUM ( Table1[Sales] ), SetOfDatesYTD, KEEPFILTERS ( 'table 1'[Vendor code] ) ) RETURN Result
If this response was helpful in any way,
I’d gladly accept a kudo.
Please mark it as the correct solution.
It helps other community members find their way faster.Hi,
Thanks for everyone for assisting. I truly appreciate, But the response is not working. I cannot upload any file due to restrictions. Closing this, Thanks for every one.
Super UserHello santoshlearner2
for MTD try with below DAX Code
MTD Sales =
SUMX (
VALUES ( Table1[Vendor code] ),
CALCULATE (
SUM ( Table1[Sales] ),
DATESMTD ( DimDatesQ[EOM] )
)
)
If my response helped you, please consider clicking
Accept as Solution ✅ and giving it a Like 👍 – it helps others in the community too.
DATESMTD() redefines the filter context
At Total level, Power BI recalculates the measure once
At Vendor level, it recalculates separately per vendor
Total ≠ Sum of vendors when time intelligence is involved
Thanks,
Connect with me on:
Resolver IIHi,
I appreciate your quick reply, but getting the same error, am i doing something wrong.
Measure 1 : Total Sales = CALCULATE(SUMX(Table 1( Sales))
Measure 2: Total Sales MTD = CALCULATE([Total Sales],DATESMTD (DIMDatesQ[Date]))
Measure 3: Total Inflows by distributors= SUMX ( VALUES (table 1 ( vendor code), CALCULATE ([Total Sales MTD], DATESMTD ( DIMDatesQ[Date]))) )
- Ashish_Mathur
Hi,
Share the download link of the PBI file.
- pcoley
santoshlearner2 please check the syntax of the measures you wrote in your post. I´m pretty sure the measures at your pbi are wroten in a different way and it´s hard to us trying to guess the exact way you really wrote them.
If possible try to share some of the values of the table and the results. i mean, with the example given, all should work as expected; but the bug is still there in your results.
It can be due to data duplicated, or due to the context or relationships, or... whith real data and some info of your model we can try to guess what should cause the persistent issue.- pcoley
santoshlearner2 Please check which MonthDate-field are you using in the report.
One common mistake is to have a DateDimTable, but use the Date-field from a transaction table.
Best practice: Use the DateDimTable Field at the report
Alternate solution if you are using the Table1[Month] field at the report:Sales Amount MTD := VAR LastVisibleDate = MAX ( 'Table1'[Month] ) VAR CurrentYear = YEAR ( LastVisibleDate ) VAR CurrentMonth = MONTH ( LastVisibleDate ) VAR SetOfDatesMTD = FILTER ( ALL ( 'Table1'[Month] ), 'Table1'[Month] <= LastVisibleDate && YEAR ( 'Table1'[Month] ) = CurrentYear && MONTH ( 'Table1'[Month] ) = CurrentMonth ) VAR Result = CALCULATE ( SUM ( Table1[Sales] ), SetOfDatesYTD, KEEPFILTERS ( 'table 1'[Vendor code] ) ) RETURN Result
If this response was helpful in any way,
I’d gladly accept a kudo.
Please mark it as the correct solution.
It helps other community members find their way faster.
