Require Help on DAX Logic

[Dharma]

Hello Team,

I hope you guys can help me in the below scenario where I have to prepare weekly report on Hired employees ideally on Monday of every week, where I have to show the below.

1. Hired Employees every week.
2. Percentage of change(Positive or negative) compared to Previous week date.

I have created the report where am getting the correct values overall but when I apply filter like country or any other filter I will get wrong previous week Percentage.

I guess this is something to do with ALL function where am not able to figure out, Any help from the team is appreciated.

I have attached the PBIX file and Sample xls file for your reference.
Previous_week_PBIX.pbix (50.1 KB)
Sample_Week_Data.xlsx (12.2 KB)

[jamie.bryan]

Hi @Dharma

If you change your previous week function to ALLSELECTED rather than ALL, it should work when you select a country/department:

Previous week = 
    CALCULATE([Total requisitions], 
        FILTER(ALLSELECTED(Table1), 
            Table1[Week of Year]  = SELECTEDVALUE(Table1[Week of Year]) -1))

You can then change your From Previous Week % column to be simplier:

From Previous Week = 
    DIVIDE([Total requisitions], [Previous week],0) - 1

Previous_week_PBIX.pbix (50.6 KB)

Hope that’s what you needed!

[Dharma]

Hello @jamie.bryan

Yes It worked partially, because when I am converting the same value to card visual it is stuck at (-100%) it is not changing.

Any help on this ? based on the my filter my card visual should change as per the latest date and filter which I apply.

[jamie.bryan]

That’s stretching the limits of my knowledge a bit I’m afraid!

I believe it’s because on the card there’s no row context, so it’s essentially what the total line is showing at the bottom of the table. You may need to create extra measures for the cards, that are filtered for the latest week that is visible after your filters - hopefully someone may be able to chime in with that.

[BrianJ]

@Dharma ,

@jamie.bryan is spot on. For card visuals, you need to build up the proper context for the calculation using virtual tables. This video demonstrates exactly how to do that.

Fixing Card Visual Results In Power BI

• Brian

[Dharma]

Hello @BrianJ ,

Not sure am building the logic in a right manner , I have tried the same but not getting the desired result.

1 In first image you can see I should get 25 % in card based on the latest date value.
2. In second image I should get 0% based on my latest date value, Country and Department filter.

Can you please help me with the DAX to achieve the above
Previous_week_PBIX.pbix (50.6 KB)

[JBocher]

Hi @Dharma,

To solve your problem, I have created several measures.

First I needed to rank the dates in order to have only the last value of your From Previous Week measure. Then I’ve only kept the value of the highest date.

The second part is to calculate the total with a virtual table as Brian said earlier

Here is the table to better understand what I have done.

I hope this solution will help you

Here is the solution :
Previous_week_PBIX.pbix (59.0 KB)

Best regards,
Joaly

[Melissa]

Hi @Dharma,

I seem to be the only one who is a little confused here. Let’s try to clear that up, shall we…
Q. Are you doing any time intelligence calculations? By that I mean visualizing a date attribute and/or adjusting that attribute’s context??

If the answer is yes, why is there no date table in your model?
I applaude all who ventured into this quest but incorporating a Date table seems obvious to me.
Please review the ultimate beginners guides in the learning portal.

Previous week = 
    CALCULATE( [Total requisitions], 
        DATEADD(Dates[Date], -7, DAY )
    )

and

Fixed Card = 
VAR _LastWeek = CALCULATE( MAX( Table1[Date] ), REMOVEFILTERS( Dates[Date] ))
RETURN
IF( ISINSCOPE( Dates[Date] ),
    [From Previous Week],
    CALCULATE( [From Previous Week], Dates[Date] = _LastWeek )
)

.
With this result

Here’s your sample file.
Previous_week_PBIX.pbix (50.1 KB)

I hope this is helpful.

[JBocher]

This is more simple indeed !
Thank you @Melissa

[Melissa]

Thanks @JBocher
I like “simple” things

[Dharma]

Thanks a ton @Melissa you saved my day.

You are a Simple GURU in EDNA…