Calculate ranks dynamically based on Slicer

[Anonymous]

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!

[afzalphatan]

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

[nickchobotar]

@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]

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]

@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 -

[Anonymous]

Well, there's a challenge. DirectQuery is a requirement, but DirectQuery doesn't support relationships between HANA views. I basically need two measures:

1: total until the maximum date, while ignoring minimum date

2: total until the minimum date, while ignoring anything above.

[nickchobotar]

@Anonymous

At this point it will be best if you share with us some data samples with the end result.

N -

[Anonymous]

Hi @nickchobotar

Here's the sample .PBIX file: https://we.tl/bsdSlQ6G1P

Looking back at my question, there are two measures that I need to create.  First of all my report has a minimum and maximum date in a slicer.

1. A measure that sums all stock until the minimum slicer date

2. A measure that sums all stock until the maximum slicer date

as mentioned before, because I'm using DirectQuery on SAP HANA, the following actions are impossible:

- Create a date table;

- Create relationships between tables.

Thanks in advance

[nickchobotar]

@Anonymous

Give this a try and see if it returns the right logic

MinStock = 
VAR MinDate = MIN(Table1[Date]) RETURN
CALCULATE(
    SUM(Table1[Stock Change]),
    DATESBETWEEN(Table1[Date], MIN(Table1[Date]), MinDate)

)

MaxStock = 
VAR MaxDate = MAX(Table1[Date]) RETURN
CALCULATE(
    SUM(Table1[Stock Change]),
    DATESBETWEEN(Table1[Date], MIN(Table1[Date]), MaxDate)

)

Nick -

[Anonymous]

Hi Nick,

The logic keeps applying the slicer to the variables if I change it.

[nickchobotar]

@Anonymous

I was working off these requirements:

"

1. A measure that sums all stock until the minimum slicer date

2. A measure that sums all stock until the maximum slicer date

"

Any chance you could post a very brief 10 row max sampling data and show exact the end result too. This should eliminate all the other questions.

N-

[v-xjiin-msft]

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]

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.