Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Calculate ranks dynamically based on Slicer

Hi There,

 

Currently I'm attempting to calculate a starting value based on range of data ranked containing goods issues and goods receipts by date. The problem I have is that my measure isn't working with my date slicer.

This is my expression:

StartingStock =
SUM(zMaterialWarehouseStockMovementQueryPBI[QuantityReceipts])
-
CALCULATE(SUM(zMaterialWarehouseStockMovementQueryPBI[QuantityReceipts]);
zMaterialWarehouseStockMovementQueryPBI[StockMovementCategory]="Goods Receipts")
-
CALCULATE(SUM(zMaterialWarehouseSTockMovementQueryPBI[QuantityReceipts]);
zMaterialWarehouseStockMovementQueryPBI[StockMovementCategory]="Goods Issues")

Any ideas on how to solve this?

Thanks in Advance!

Need Help
Show and Tell

12 Replies

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    Hi Anonymous,

     

    In your scenario, how did you specify this date slicer? Were you using a calendar table? Or it is just a date column from the table zMaterialWarehouseStockMovementQueryPBI? If you were using a calendar table, have you created a relationship between the two tables?

     

    Please share us more detailed information about your issue like some sample data which can help us repro your issue or some screenshots about your report. 

     

    Thanks,
    Xi Jin.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      I am simply using a data column from the HANA Live view. Since I'm using DirectQuery and am only allowed to connect to a single HANA view.

  • nickchobotar's avatar
    nickchobotar
    Icon for Skilled Sharer rankSkilled Sharer
    8 years ago

    Anonymous

     

     

    Please share your data model and meanwhile you can test this measure. Make sure you have a relationship between zMaterialWarehouseStockMovementQueryPBI table and Data table.

     

     

    CALCULATE (
    SUM ( zMaterialWarehouseStockMovementQueryPBI[QuantityReceipts] ),
    FIRSTNONBLANK (
        'Date'[Date],
        CALCULATE (
            SUM ( Inventory[UnitsBalance] ),
            zMaterialWarehouseStockMovementQueryPBI[StockMovementCategory]= "Goods Receipts",
            zMaterialWarehouseStockMovementQueryPBI[StockMovementCategory] = "Goods Issues"
        )
    )
)

     

    N -

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      My data model consists of a single table (HANA view), where I connect to using DirectQuery. I don't have a date table but simply a date column with posting dates.

      • nickchobotar's avatar
        nickchobotar
        Icon for Skilled Sharer rankSkilled Sharer
        8 years ago

        Anonymous

         

        You are trying to do a time intelligence here and in Power BI / Tabular echo system you will need a data table for that. Basically, you have two options. Bring your data in not as DirectQuery but as an import and then create a data table in M or keep DirectQuery but create a data table in your SAP source.

         

        N -

  • afzalphatan's avatar
    afzalphatan
    Icon for Resolver I rankResolver I
    8 years ago

    Can you post sample data and expected result ... that will have more clarity