Forum Discussion

insandur's avatar
insandur
Helper II
1 year ago

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

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.


Need Help

6 Replies

  • Uzi2019's avatar
    Uzi2019
    Community Champion
    1 year ago

    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's avatar
      insandur
      Helper II
      1 year ago

      Thank you Uzi,

       

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

       

      • Uzi2019's avatar
        Uzi2019
        Community Champion
        1 year ago

        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!

         

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      insandur
      Helper II
      1 year ago

      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's avatar
        Anonymous
        Not applicable
        1 year ago

        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