Calculation for last date where flag is set to one

[lokosrio]

hi,

I need a help with a calculation.

The formula looks like this:

DIVIDE(
    SUMX(VALUES(table[region]), LASTNONBLANKVALUE(table[date],
        SUM(table[flag]))
    ),
    SUMX(VALUES(table[region]), LASTNONBLANKVALUE(table[date],
        COUNTROWS(table))
    )
)

but I do no want to have last date, instead of this I need date where at least one row have a flag = 1, for example:

region	date	flag	sales
us	08.03.2021	0	10
us	08.03.2021	1	20
us	09.10.2021	0	30
us	09.10.2021	0	40

Insted of calculation for 09.10.2021 I want to calculate data for 08.03.2021, because there at least one row has flag = 1.

Could you please help me with this?

[richbenmintz]

Hi @lokosrio,

Are you able to provide the expected outcome, I am not sure I quite understand the requirement for the formula

[lokosrio]

hi @richbenmintz 

I want to get the sum divided by count of rows, but it should be done on the region level.

In above example I want to sum the flags (in this case 1), dividing by count of rows (in this case 2) for the date, where at least one flag = 1 the on a region level, so the result will be 0,5.

Other example:

region	date	flag
de	08.03.2021	0
de	08.03.2021	0
de	09.10.2021	1
de	09.10.2021	0
de	09.10.2021	1
us	08.03.2021	0
us	08.03.2021	1
us	09.10.2021	0
us	09.10.2021	0

in this case eu = 2/3 = 0,6 and us = 1/2 = 0,5
Sorry that I forgot to mention, but on the visualization (simple table) there will be more dimensions and filters. If something is still not clear then please let me know.

[richbenmintz]

Hi @lokosrio ,

Given the data provided i did the following:

1. Created a Calc Column to define where the dates had flag

date_has_flag = 
var _region = 'sample'[region]
var _date = 'sample'[date]
return
if(CALCULATE(SUM('sample'[flag]), FILTER('sample', 'sample'[region] = _region && 'sample'[date] = _date))>0, 1, 0) 

2. Created a measure that used the calc column value to identify where the calc needs to take place

pct per region = 
DIVIDE(
    CALCULATE(COUNTROWS('sample'), 'sample'[date_has_flag] = 1, 'sample'[flag] = 1), CALCULATE(COUNTROWS('sample'), 'sample'[date_has_flag] =1))

gets the following result

Hope this works for you

[lokosrio]

hi @richbenmintz,

it looks good, I added a small modification to get max date where the flag = 1 grouped by region, but it needs to be changed dynamically - I have a month filter on visualization, so the date_has_flag needs to consider latest date for selected months.

When I choose january 2021:

region	date	flag	date_has_flag
us	07.01.2021	1	1
us	07.01.2021	1	1
us	08.03.2021	0	0
us	08.03.2021	1	0
us	09.10.2021	0	0
us	09.10.2021	0	0

when I choose for example january - march 2021, january-october, etc. or whole 2021:

region	date	flag	date_has_flag
us	07.01.2021	1	0
us	07.01.2021	1	0
us	08.03.2021	0	1
us	08.03.2021	1	1
us	09.10.2021	0	0
us	09.10.2021	0	0

[richbenmintz]

Hi @lokosrio ,

I am not really following, the date_has_flag column depends on the flag and region columns and does not care about the date, a date either has a flag or it does not, how you filter on the period is not really effected. 

Thanks,

[lokosrio]

hi @richbenmintz 

I will try to simplify it. First of all I modified your calculated column:

if(CALCULATE(SUM('sample'[flag]), FILTER('sample', 'sample'[region] = _region && CALCULATE(MAX('sample'[date]),
ALLEXCEPT('sample', 'sample'[region]), sample[flag] = 1) = _date))>0, 1, 0) 

it is fine, but the date_has_flag is fixed on table level.

I want to change it - date_has_flag needs to change dynamically and be depended on period filter.

Sample data:

product	region	date	flag
prod_1	de	08.03.2021	0
prod_2	de	08.03.2021	0
prod_3	de	09.10.2021	1
prod_4	de	09.10.2021	0
prod_5	de	09.10.2021	1
prod_6	us	07.01.2021	1
prod_7	us	07.01.2021	1
prod_8	us	08.03.2021	0
prod_9	us	08.03.2021	1
prod_10	us	09.10.2021	0
prod_11	us	09.10.2021	0

Example1: When I choose january 2021 in the period filter

the result on the visualization is:

region	pct_per_region
de	0
us	1

it is because there is no data for de in january and for us the latest date, where at least one row has flag = 1 is on 07.01.2021, so sum of flag is 2 devided by count of rows 2 = 1

Example2: when I choose for example january - november 2021 in the period filter:

region	pct_per_region
de	0,6
us	0,5

latest date, where at least one row has flag= 1 for de is 09.10.2021, so the result is 2/3 = 0,67

latest date, where at least one row has flag = 1 for us is 08.03.2021, so the result is 1/2 = 0,5

Please let me know if you get the point.

[v-yiruan-msft · Accepted solution]

Hi @lokosrio ,

You can create a measure as below to get the percentage of per region, please find the details in the attachment.

pct_per_region =
VAR _latestdate =
    CALCULATE (
        MAX ( 'Table'[date] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[region] = SELECTEDVALUE ( 'Table'[region] )
                && 'Table'[flag] = 1
        )
    )
VAR _countflag1 =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[product] ),
        FILTER (
            'Table',
            'Table'[region] = SELECTEDVALUE ( 'Table'[region] )
                && 'Table'[flag] = 1
                && 'Table'[date] = _latestdate
        )
    )
VAR _countldate =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[product] ),
        FILTER (
            'Table',
            'Table'[region] = SELECTEDVALUE ( 'Table'[region] )
                && 'Table'[date] = _latestdate
        )
    )
RETURN
    DIVIDE ( _countflag1, _countldate, 0 )

Best Regards

[richbenmintz]

Hi @lokosrio,

try this, with the original date_has_flag calculation

pct per region = 
var _max_date = CALCULATE(MAX('sample'[date]), 'sample'[flag] =1)
return
DIVIDE(
    CALCULATE(COUNTROWS('sample'), 'sample'[date_has_flag] = 1, 'sample'[flag] = 1, 'sample'[date] = _max_date), 
    CALCULATE(COUNTROWS('sample'), 'sample'[date_has_flag] =1, 'sample'[date] =_max_date))