CODEWITHSUNDEEP
powerbi
eponkratova
eponkratova

Reputation: 477

If first day of the month take value for the last day of the month - Power BI

I have the following dataset: enter image description here

What I need is to correct the quantity amount i.e. if the day is the first day of the available dataset, take the quantity on the last day in this dataset in the previous month.

enter image description here

I am working on:
1. Added 2 col where I indicate the start of the month in the available dataset; in the 2nd col, I am indicating the last day of the month in the available dataset.

  1. Then, I created a measure and the idea is that I am checking if the [date] is equal to [start_date]. If it is, I want to return the [quantity] from the [prev_month_end]. Otherwise, return [quantity] for the current date. I feel that I am very close, but not getting the desired result.po
start_date = STARTOFMONTH('tbl'[date])
prev_month_end= LASTDATE ( PREVIOUSMONTH ( 'tbl'[date] ))

quantity_corrected = 
var tdate = 'tbl'[prev_month_end]
var warehouse = 'tbl'[warehouse_code]
var prod_cat = 'tbl'[product_category]
var prod_typ = 'tbl'[product_type]

return
if(
    'tbl'[date]='tbl'[start_date],
    CALCULATE(
        FIRSTNONBLANK('tbl'[quantity],1),
        FILTER(
            ALL('tbl'), 
            'tbl'[product_category]=prod_cat && 
            'tbl'[prev_month_end] = tdate && 
            'tbl'[warehouse_code]=warehouse && 
            'tbl'[product_type]=prod_typ
        )
    ),
    'tbl'[quantity]
)

Upvotes: 1

Views: 1014

Answers (2)

Aldert
Aldert

Reputation: 4323

You where pretty close, I corrected the comparison in the if and used Sum because I know I get only one row in the filter. Be aware, your logic is not working when you have gaps in your data. e.g. 31 Jan is missing..

quantity_corrected = 
    var tdate = 'tbl'[prev_month_end]
    var warehouse2 = tbl[warehouse_code]
    var prod_cat = tbl[prod_category]
    var prod_typ = tbl[product_type]
    var base_filter = FILTER(tbl; prod_cat = tbl[prod_category] && prod_typ = tbl[product_type] && warehouse2 = tbl[warehouse_code])
return 
    if (tbl[date] - 1 = tdate; 
        CALCULATE(SUM(tbl[quantity]);FILTER(base_filter;tbl[date] = tdate));
        tbl[quantity])

Upvotes: 1

OscarLar
OscarLar

Reputation: 1335

Not completely sure I understand this but I think you only have to change one argument in the FILTER function.
Also, I added a second nested IF-statement (with the temp variable), otherwise the first row in the [quantity_corected] becomes blank.

quantity_corrected = 
var tdate = 'tbl'[prev_month_end]
var warehouse = 'tbl'[warehouse_code]
var prod_cat = 'tbl'[prod_category]
var prod_typ = 'tbl'[product_type]

return
if(
    'tbl'[date]='tbl'[start_date];
    var temp =
    CALCULATE(
        FIRSTNONBLANK('tbl'[quantity];1);
        FILTER(
            ALL('tbl');
            'tbl'[prod_category]=prod_cat && 
            'tbl'[date] = tdate &&  // <-- Changed from [prev_month_end]
            'tbl'[warehouse_code]=warehouse && 
            'tbl'[product_type]=prod_typ
        )
    )
    return
    IF(
        temp <> BLANK();
        temp;
        'tbl'[quantity]
    );
    'tbl'[quantity]
)

Resulting table:

enter image description here

Upvotes: 1

Related Questions