Measure not working when using in table when other columns added in the table and applied filters

[insandur]

Hi Experts ,

I have table with multiple columns and using measure to get previous month value for selected month "Prev month Qty"

Now this measure doesnot work in the table when i add few more columns to the table, when i remove effected column, measure works. Also same with filter, when filter is applied meaure value dont work.

below is the effected column snapshot and green marked is the measure which is not working.

Thank you in advance.

[Anonymous]

Hi,

Thanks for the solution Uzi2019 offered, and i want to offer some more information for user to refer to.

hello @insandur , you can try the following code.

Pre month N2 to N7 =
CALCULATE (
    SUM ( 'FC Data'[N2 to N7 Avg] ),
    ALLSELECTED ( 'FC Data' ),
    EOMONTH ( 'FC Data'[FC Month], 0 ) = EOMONTH ( MAX ( 'FC Data'[FC Month] ), -1 )
)

Best Regards!

Yolo Zhu

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

[insandur]

Thank You Yolo,

i tried above method, even this is giving me blank values when i apply filter 'Approval Status' column. I get correct numbers when no filter is applied. Attaching the BI sample file.

  

after the filter

[Anonymous]

Hi @insandur 

Thank you for your quick reply. Could you provide some sample data? Because I cannot create some sample data according to your description, so I can't provide you with more suggestions and solutions.

Best Regards!

Yolo Zhu

[Uzi2019]

Hi @insandur 

Try this measure for previous monh calcuation

PrevMonth= Calculate(Sum(sales), DateAdd( 'Calendar'[Date], -1, Month))
Or

Prev Month= Calcuate ( Sum( Sales), ParallelPeriod( 'Calendar'[Date],-1, Month))

I hope I answered your question!

[insandur]

Thank you Uzi,

I just get total value, and blank rows when i try above method.

[Uzi2019]

HI @insandur 

If you dont have contineous date then you may not get the result.

Try to create calendar table then use the same measure.

Calendar Tabel= Calendar( Min(FC month), Max(FC month))


Make sure your FC month is date format should not be a text data type.

I hope I answered your question!