Calculate error % of previous week

[keivan]

Hi all,

I have a dataset with several tables. The 2 i am taking in consideration are the date table and a table called fetch
fetch table containes all the information of downloads made for each connection. In this table I calculated a measure which analyse how many time the download got an error. I made this measure also in a % way

While date table is made with the following expression

date =

ADDCOLUMNS (

CALENDAR( DATE(2020,01,01), DATE(2025,12,31)),

"year", YEAR ( [Date] ),

"month name", FORMAT ( [Date], "mmmm" ),

"month number", MONTH ( [Date] ),

"week number", WEEKNUM([Date],2),

"day number", DAY( [Date])

)

I would like to report in a single table the error % on download got on a single day and the average error of the previous week. So that i may highlights if eeror rose recently

I try to do the following expression but I got an error:

True / False expression does not specify a column. Each True / False expression used as a table expression must refer to exactly one column.

Previous Week Authorize KO =

VAR CurrentWeek = MAXX('date', 'date'[week number])

VAR CurrentYear = MAXX('date','date'[year])

VAR MaxWeekNumber = CALCULATE('date', MAXX('date','date'[week number]), ALL('date'))

VAR AutKOWeek =

IF(

ISFILTERED('date'[week number]),

CALCULATE(

SUMX('fetches', [authorize_ko_technical%]

),

FILTER(

ALL('date'),

IF(

SELECTEDVALUE('date'[week number]) = 1,

'date'[year] = CurrentYear -1 && 'date'[week number] = MaxWeekNumber,

'date'[year] = CurrentYear && 'date'[week number] = CurrentWeek -1

)

)

),

[authorize_ko_technical%]

)

RETURN

IF(

ISBLANK([authorize_ko_technical%]),

BLANK(),

AutKOWeek

)

 


Can you please help me 😞

[v-shex-msft]

Hi @keivan,

Any update on these? Can you please share a pbix or some dummy data that keep the raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[keivan]

Hi Xiaoxin

I try to attach the pbix file but i got this error The file type (.pbix) is not supported.

Below I attached some screenshot and further detail.
Thanks,

Keivan

[keivan]

Hi @tamerj1 

thanks for your help.

Unfortunately still getting the same error

True / False expression does not specify a column. Each True / False expression used as a table expression must refer to exactly one column.

[tamerj1]

@keivan 
Would you please share a screenshot?

[keivan]

Sorry for the late reply.

Here the screenshot. 
I'll try also to share some dummy data

[tamerj1]

@keivan 

Seems there is a typo mistake in line no. 7

Previous Week Authorize KO =
VAR CurrentWeek =
    MAXX ( 'date', 'date'[week number] )
VAR CurrentYear =
    MAXX ( 'date', 'date'[year] )
VAR MaxWeekNumber =
    CALCULATE ( MAXX ( 'date', 'date'[week number] ), ALL ( 'date' ) )
VAR AutKOWeek =
    IF (
        ISFILTERED ( 'date'[week number] ),
        IF (
            SELECTEDVALUE ( 'date'[week number] ) = 1,
            CALCULATE (
                SUMX ( 'fetches', [authorize_ko_technical%] ),
                FILTER (
                    ALL ( 'date' ),
                    'date'[year] = CurrentYear - 1
                        && 'date'[week number] = MaxWeekNumber
                )
            ),
            CALCULATE (
                SUMX ( 'fetches', [authorize_ko_technical%] ),
                FILTER (
                    ALL ( 'date' ),
                    'date'[year] = CurrentYear
                        && 'date'[week number] = CurrentWeek - 1
                )
            )
        ),
        [authorize_ko_technical%]
    )
RETURN
    IF ( ISBLANK ( [authorize_ko_technical%] ), BLANK (), AutKOWeek )

[keivan]

Thanks Actually there was a typo.

However unfortunately the result it seems uncorrect.

Here a screenshot 

In the week from 10 to 16 october client A register a 9% error on the weekly base.
However when in the table I select any day of the week from 17 to 23 october (also if i select the all week) the result is an average error of 7%. 

I check for several clients and it look that often the number do not match

[tamerj1]

@keivan 

Please try

Previous Week Authorize KO =
VAR CurrentWeek =
    MAXX ( 'date', 'date'[week number] )
VAR CurrentYear =
    MAXX ( 'date', 'date'[year] )
VAR MaxWeekNumber =
    CALCULATE ( MAXX ( 'date', 'date'[week number] ), ALLSELECTED ( 'date' ) )
VAR AutKOWeek =
    IF (
        ISFILTERED ( 'date'[week number] ),
        IF (
            SELECTEDVALUE ( 'date'[week number] ) = 1,
            CALCULATE (
                SUMX ( 'fetches', [authorize_ko_technical%] ),
                FILTER (
                    ALL ( 'date' ),
                    'date'[year] = CurrentYear - 1
                        && 'date'[week number] = MaxWeekNumber
                )
            ),
            CALCULATE (
                SUMX ( 'fetches', [authorize_ko_technical%] ),
                FILTER (
                    ALL ( 'date' ),
                    'date'[year] = CurrentYear
                        && 'date'[week number] = CurrentWeek - 1
                )
            )
        ),
        [authorize_ko_technical%]
    )
RETURN
    IF ( ISBLANK ( [authorize_ko_technical%] ), BLANK (), AutKOWeek )

[keivan]

Unfortunately didn't change the result:

maybe it is due on how is calculated authorize_ko_technical measure?

Or maybe is the sumx function. I am actually looking for an average.
Thanks again 

authorize_ko_technical%= 'fetches'[authorize_ko_technical]/('fetches'[authorize_ko_technical]+ 'fetches'[authorize_ok])

[tamerj1]

@keivan 

What are you trying to achieve by this condition?

IF(

SELECTEDVALUE('date'[week number]) = 1,

[keivan]

Hi,

sorry to bother you again.
As you may guess I struggle a bit with dax formula more complicate than a Calculate 😞 
Before opening a task on the community I tried to seek for other issues similar. So I tried with some copy paste. Indeed this was one of those. But Actually I don't fully understand the logic behind, as well the one in your formula 😞
I'm improving but some stuff is still out of my league

[tamerj1 · Accepted solution]

@keivan 
I think now I understand the measure. However, it could have been much more simplier if you date tabe has a week sequential number that contimues to grow over years (does not restart every year). This can be achieved by calculated column using a dense rank over the [Year] * 100 + [week number]

However, based on the same method, please try

Previous Week Authorize KO =
VAR CurrentWeek =
    MAXX ( 'date', 'date'[week number] )
VAR CurrentYear =
    MAXX ( 'date', 'date'[year] )
VAR MaxWeekNumber =
    CALCULATE (
        MAXX ( 'date', 'date'[week number] ),
        ALL ( 'date' ),
        'Date'[year] = CurrentYear - 1
    )
VAR AutKOWeek =
    IF (
        ISFILTERED ( 'date'[week number] ),
        IF (
            SELECTEDVALUE ( 'date'[week number] ) = 1,
            CALCULATE (
                [authorize_ko_technical%],
                FILTER (
                    ALL ( 'date' ),
                    'date'[year] = CurrentYear - 1
                        && 'date'[week number] = MaxWeekNumber
                )
            ),
            CALCULATE (
                [authorize_ko_technical%],
                FILTER (
                    ALL ( 'date' ),
                    'date'[year] = CurrentYear
                        && 'date'[week number] = CurrentWeek - 1
                )
            )
        ),
        [authorize_ko_technical%]
    )
RETURN
    IF ( NOT ISBLANK ( [authorize_ko_technical%] ), AutKOWeek )

[keivan]

Thank You so much 🙂 

[keivan]

Ok I'm not able to load a pbix file.

I try with several screenshot.
I reduce the model to 3 table. (a calendar, a client table, and a consent table)

In my business case, I want to analyze how many people on a web site provide the consent to access their personal data.

This is the consent table in which I have  the client who perform the connection, the status and the date 

My final table would be something like this

Everyday I want to monitor the conversion rate (consent ok/Consent Total) for each of my client. The tough point is that I want also to compare with previous week rolling average. So that i can easily spot witha delta if the performance of one of my client abruptly fall. I choose the previous week average beacuase it is a more stable data than the previous day.

I have calculated also some measure:

Consent Total = CALCULATE(SUM('consents'[consent_ok] ) + SUM('consents'[consent_new]) + SUM('consents'[consent_fail]))
 and

conversion rate = CALCULATE(SUM('consents'[consent_ok])/[Consent Total])

now i would like to calculate consent_ok% 

[tamerj1]

Hi @keivan 

Please try

Previous Week Authorize KO =
VAR CurrentWeek =
    MAXX ( 'date', 'date'[week number] )
VAR CurrentYear =
    MAXX ( 'date', 'date'[year] )
VAR MaxWeekNumber =
    CALCULATE ( 'date', MAXX ( 'date', 'date'[week number] ), ALL ( 'date' ) )
VAR AutKOWeek =
    IF (
        ISFILTERED ( 'date'[week number] ),
        IF (
            SELECTEDVALUE ( 'date'[week number] ) = 1,
            CALCULATE (
                SUMX ( 'fetches', [authorize_ko_technical%] ),
                FILTER (
                    ALL ( 'date' ),
                    'date'[year] = CurrentYear - 1
                        && 'date'[week number] = MaxWeekNumber
                )
            ),
            CALCULATE (
                SUMX ( 'fetches', [authorize_ko_technical%] ),
                FILTER (
                    ALL ( 'date' ),
                    'date'[year] = CurrentYear
                        && 'date'[week number] = CurrentWeek - 1
                )
            )
        ),
        [authorize_ko_technical%]
    )
RETURN
    IF ( ISBLANK ( [authorize_ko_technical%] ), BLANK (), AutKOWeek )