Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
augustindelaf
Impactful Individual
Impactful Individual

filter some data only on last month of a date slicer

hello All, 

 

 

I am not very familiar with Dax formulas, and i have the following issue :

 

how can I filter some visuals to the last month of a date slicer, while other visuals are filtered by the whole date slicer? 

thank you.

the solution that I found until now was to create two slicers next to each other, but with independant filtering : one with the whole time range, one only with month value.

 

i hope i am clear !

thank you

1 ACCEPTED SOLUTION

@augustindelaf

 

Hi, Use the same logic to the rest of the fields or measures.

 

 

VarKPI2MonthlyTarget/RCFT =
VAR LastMonth =
    DATE ( YEAR ( MAX ( 'DATA(Update KPIs)'[KPI Update Date] ) ), MONTH ( MAX ( 'DATA(Update KPIs)'[KPI Update Date] ) ), 01 )
RETURN
    IF (
        'DATA(Update KPIs)'[SelectedValue] = "WS 3.5",
        FORMAT (
            CALCULATE (
                AVERAGE ( 'DATA(Update KPIs)'[KPI 2 Monthly Target/RFCT] ),
                FILTER ( 'DATA(Update KPIs)', 'DATA(Update KPIs)'[KPI Update Date] = LastMonth )
            ),
            "Percent"
        ),
        FORMAT (
            CALCULATE (
                SUM ( 'DATA(Update KPIs)'[KPI 2 Monthly Target/RFCT] ),
                FILTER ( 'DATA(Update KPIs)', 'DATA(Update KPIs)'[KPI Update Date] = LastMonth )
            ),
            "Currency"
        )
    )



Lima - Peru

View solution in original post

18 REPLIES 18
Greg_Deckler
Super User
Super User

In theory you could use one slicer but set a visual level filter to another calculated measure that was the last month of whatever the date slicer was. Would need some data to mock up to be sure this would work, but in theory...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler,

 

thank you for answering me.

yes, I guess the visual level filter would be a calculated column (or a measure, right?) with a name like "relativeLastMonthFlag".

 

Do you have any idea of how to create this measure / calculated column?

 

in my data set I have : a 'values' column (tagged as decimals), a "date" column (tagged as date with days, months and years)

 

Many Thanks in advance

Hi @augustindelaf,

 

You can take a look at below formulas:

 

Calculate column:
Use current date as the condition to add flag.
LastMonthFlag(Today) = if(FORMAT([Date],"mmmm yyyy")=FORMAT(TODAY(),"mmmm yyyy"),"Las tMonth","Other") 

Use last date of table as the condition to add flag.
LastMonthFlag(LastDate) = if(FORMAT([Date],"mmmm yyyy")=FORMAT(LASTDATE(ALLSELECTED('Table'[Date])),"mmmm yyyy"),"Las tMonth","Other") 

Measure:
LastMonthFlag2(LastDate) = if(FORMAT(MAX('Table'[Date]),"mmmm yyyy")=FORMAT(LASTDATE(ALLSELECTED('Table'[Date])),"mmmm yyyy"),"Las tMonth","Other") 

LastMonthFlag2(Today) = if(FORMAT(MAX('Table'[Date]),"mmmm yyyy")=FORMAT(TODAY(),"mmmm yyyy"),"Las tMonth","Other") 

 

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hello @v-shex-msft,

 

thank you for answering me.

in your answer, what is interesting for me is the Measure "LastMonthFlag2(LastDate)"    because I want the system to work in the current filter context. 

 

I tried to implement it but it did not work. do you know why?

To be sure, I repeat my need : 

Values in the yellow circle should be filtered only by last month of the slicer (in yellow as well).

Capture.PNG

should the measure be implemented as the new value field or as a visual level filter? (personnally, I put it as a visual level filter) .

PS: forget about date format, I don"t care. 

 

If you can solve that I would be very happy !

Best Regards,

 

Augustin

Hello @v-shex-msft@Greg_Deckler,

 

 

 

no idea of how to do that?

I can send you my PBIx if you want !

I would be glad to solve this pblem, as I'm sure I'll meet other clients asking such a system.

 

thanks

Augustin de la fouchardiere

BI Consultant at CGI 

Hi @augustindelaf,

 

Can you share the sample data to test?

 

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.

Hi @v-shex-msft,

 

File has been uploaded.

Link is here:

https://drive.google.com/open?id=0B0os9aXobQDBMENXMTI5NHJtcVE

 

best Regards,

Augustin

@augustindelaf

 

Hi, Use the same logic to the rest of the fields or measures.

 

 

VarKPI2MonthlyTarget/RCFT =
VAR LastMonth =
    DATE ( YEAR ( MAX ( 'DATA(Update KPIs)'[KPI Update Date] ) ), MONTH ( MAX ( 'DATA(Update KPIs)'[KPI Update Date] ) ), 01 )
RETURN
    IF (
        'DATA(Update KPIs)'[SelectedValue] = "WS 3.5",
        FORMAT (
            CALCULATE (
                AVERAGE ( 'DATA(Update KPIs)'[KPI 2 Monthly Target/RFCT] ),
                FILTER ( 'DATA(Update KPIs)', 'DATA(Update KPIs)'[KPI Update Date] = LastMonth )
            ),
            "Percent"
        ),
        FORMAT (
            CALCULATE (
                SUM ( 'DATA(Update KPIs)'[KPI 2 Monthly Target/RFCT] ),
                FILTER ( 'DATA(Update KPIs)', 'DATA(Update KPIs)'[KPI Update Date] = LastMonth )
            ),
            "Currency"
        )
    )



Lima - Peru

@Vvelarde

 

My client finally accepted to implement your solution, and as you can see, I put a card with "MaxDate" formula next to each "Monthly" visual, to say to the user which month has the last value (may 2017 on screenshot).

 

This solution is a big step forward. Without such a solution we needed to have 2 date slicers.solved.PNG

 One big thing : can you explain how the formula works, step by step ? it there some documentation?

many thanks !!

 

 

@augustindelaf

 

I'm happy to help you.

 

The formula in simple words works in this way:

 

VarKPI2MonthlyTarget/RCFT =
//Take the last Day selected in the Slicer (MAX Function) and extract the Year and Month. and Use
// The Date Function to create a date with This. Example: Date(2017,5,1) The 1 is the first day in every month. VAR LastMonth = DATE ( YEAR ( MAX ( 'DATA(Update KPIs)'[KPI Update Date] ) ), MONTH ( MAX ( 'DATA(Update KPIs)'[KPI Update Date] ) ), 01 ) RETURN IF ( 'DATA(Update KPIs)'[SelectedValue] = "WS 3.5", FORMAT (
CALCULATE ( AVERAGE ( 'DATA(Update KPIs)'[KPI 2 Monthly Target/RFCT] ), //Filter the Table to only Rows with the Date is equal To LastMonth Variable
FILTER ( 'DATA(Update KPIs)', 'DATA(Update KPIs)'[KPI Update Date] = LastMonth )
), "Percent" ), FORMAT ( CALCULATE ( SUM ( 'DATA(Update KPIs)'[KPI 2 Monthly Target/RFCT] ), FILTER ( 'DATA(Update KPIs)', 'DATA(Update KPIs)'[KPI Update Date] = LastMonth ) ), "Currency" ) )

 

 




Lima - Peru

thank you so much @Vvelarde !

 

i will remove all messages after your answer with the code, except this answer with documentation, to clarify the conversation for other users in search of solutions.

I have skills too 😉 (for instance i am quite good with Excel VBA and Power Query). don't hesitate in return, i'll be glad to help you, as you just did for me !

 

thank you so much 

@Vvelarde,

 

I just learnt, while watching the following video, that it was possible otherwise 🙂     : 

https://youtu.be/Jw9ScrjDr2M?t=24m19s

 

I am using Calculate, then SUM [KPI2TargetRFCT], and Filter on "LastDate" Fx

 

testKPI2LastMonthTarget =

CALCULATE(SUM('DATA(Update KPIs)'[KPI 2 Monthly Actual]) ; LASTDATE('DATA(Update KPIs)'[KPI Update Date]))

 

Works fine as well 🙂

Hey !

 

It's a good start, because it is displaying data from last Month.

but it is not working in the current date filter context (it has to be Last Month of the "Time Range" selection)

 

then i am trying to create a measure to isolate the Last Month of the current selection in "KPI Update Date", that I will call "LastMonth Selected".

And then we need to call this field in your measure.

 

You think you can help me doing that ?

 

thanks a lot anyway, what you did is a required 1st level for us.

BR,

Augustin

@augustindelaf

 

Maybe i understand wrong- (is possible)

 

But you need that the measure change accord to the TIme Range Selection (Using the the month of the last date selected)

 

Look the image using the measure:

 

Modified KP2 Target / ForecastModified KP2 Target / Forecast




Lima - Peru

@Vvelarde,

 

I did an integrity check on your measure, yes it works in the current filter context so it is good.

The only problem is that it shows the last value, even if the Last Value was before the Last Month Selected.

 

Example : 

in Data Set,  my Last Value, "111", is from 01/04/2017. (see image with MaxDate visual)

After, no data until end of the year.

 

In my "Time Range", Last Month Selected is October (15/10/2017),  it shows 111, but it should be 0 because there is no data in October !

 

Am I clear now ?

Capture.PNG

thanks a lot @Vvelarde, we are getting close !

@augustindelaf

 

It Works 😃 Look

 

May SelectedMay SelectedDecember SelectedDecember Selected

 

Remove the Card of Actual and recreate it.




Lima - Peru

@Vvelarde It ALMOST works !

I re-created the card but still, the problem appears.

 

If "Last Month" is blank, The Monthly cards should display nothing ! Currently they diplay the value of Last Month that is not Blank, It is not exactly what I need.

 

I share the file with you again (pbi).

to do the integrity check, select "WS 2.1" and Action ID "2.1-005" .

you should find these values: (image)

 

 

Capture.PNG

In "october 2017", for instance, Monthly Values shoud be 0 for Target/RFCT and 0 for Actual.

Why? because no there is no Action with KPI Update Date in October 2017, as you can see.

but they display "8k" for Target/RFCT and "12k" for Actual, that are the values from Last Month not Blank. (August 17)

ddd.PNG

 

Pbi file with latest values is on this link :

https://drive.google.com/open?id=0B0os9aXobQDBLWJhQkM4dzg3alk

 

Many thanks in advance

 

@v-shex-msft@Greg_Deckler,

 

@Vvelarde almost found the right solution, the data is filtered on the Last Month that is not blank, I just need the data to be filtered on Last Month (Bkank or Not).

if you think you know how to do it ?

 

thanks a lot in advance.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.