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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Po_
Frequent Visitor

One slicer filters two tables on their respective latest dates

Hello there,

 

Here is my datamodel

Po__0-1706262753153.png

 

I have two fact tables : one that collects PRODUCTION and one that collects INVOICES.

There's always a gap of one month between the two. When PRODUCTION last records are from november 2023, INVOICES last records are from december 2023.

I have one CALENDAR table, which filters both tables on their record dates, and contains two flags : FG_LAST_MONTH_PRODUCTION and FG_LAST_MONTH_INVOICES, which equal 1 during the last updated month of production or invoices respectively, and 0 otherwise.

CAL_DATEFG_LAST_MONTH_PRODUCTIONFG_LAST_MONTH_INVOICES
2023-01-0100
2023-01-0200
00
2023-10-3100
2023-11-0110
10
2023-11-3010
2023-12-0101
01
2023-12-3101

 

I would like to have ONE SLICER "last updated data", which, when selected, only shows last month from PRODUCTION AND last month from INVOICES, although it's not the actual same month.

 

I created another table, LAST_MONTHS, which contains one row and is structured as such :

FLAG_LAST_MONTHLAST_MONTH_PRODUCTIONLAST_MONTH_INVOICES 
12023-11-012023-12-01

LAST_MONTHS filters both PRODUCTION and INVOICES on their dates.

 

So, I showed FLAG_LAST_MONTH as a slicer, and when a user selects 1, my measures are automatically calculated for the last available month of both tables. Works fine, except for time intelligence, which was based on the CALENDAR table... For instance :

Po__1-1706263486814.pngPo__2-1706263590432.png

 

I wish MEASURE FROM PRODUCTION MONTH OVER MONTH was 645 instead, and MEASURE FROM INVOICES YTD was 150 666 389. Is that even possible ?

 

How could I make this work and filter a whole page on both last updated dates, depending on which table is used ? I thought of a "if isselected SLICER , calculate that measure where FG_LAST_MONTH_PRODUCTION=1", or FG_LAST_MONTH_INVOICES = 1 depending on the measure, but that would require updating a looot of measures so I'd like to do it another way.

Is that possible ? Maybe with calculation groups ? 

 

Any help appreciated !

Have a nice one

6 REPLIES 6
v-junyant-msft
Community Support
Community Support

Hi @Po_ ,

Instead of having different flags in the CALENDAR table for the last month of production and invoices, consider adding a flag to mark "last updated data" for both tables. This would be a calculated column that takes the maximum of the two flags.
Use this new tag to create a slicer that filters the PRODUCTION and INVOICES tables based on the "last updated data".
Try this DAX:

MEASURE FROM PRODUCTION MONTH OVER MONTH = 
CALCULATE(
    [Your Measure],
    FILTER(
        ALL('CALENDAR'), 
        'CALENDAR'[FG_LAST_UPDATED_DATA] = 1
    ),
    SAMEPERIODLASTYEAR('CALENDAR'[Date])
)

But I still hope you can provide some sample data, it's still hard for me to see exactly what the problem is with just the relational model without data.

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi @v-junyant-msft ,

 

So sorry, I did not receive a notification for your answer and did not see it sooner ! 

 

Thanks for your help. I believe if I create this unique flag, it would sum up data from november and december for my invoices data, correct ? I wish it woul only sum up december data for invoices, and november data for production.

 

I created a sample file :

Here is the PBI :

https://drive.google.com/file/d/1Dg9f8kB5MYdsve-2LlzaJaViZsXdAQ-i/view?usp=sharing

And, if needed, here is the Excel source file :
https://docs.google.com/spreadsheets/d/184YKuL5VCuhtredD0W8w9nT56otvtvQf/edit?usp=sharing&ouid=11185...

 

When I filter on last invoiced month, I get the right information on my invoices KPI down below. But no data is displayed for production (which makes sense).

 

Po__0-1708010256659.png

When I filter on last month of production, I got the right KPI for production, but not for invoices (which also makes sense, since I'm filtering all my data on november).

Po__1-1708010397898.png

When I filter on last month, I have the right KPI for invoiced AND produced quantity, but my YTD measures are no longer able to retrieve past data : 

Po__2-1708010551220.png

 

When implementing your idea, I got the right KPI for production, but not for invoice data : 

Po__3-1708010598770.png

 

What I would need is for my KPIs to be : 

10M ; 37M ; 10M ; 30M

 

Hope this makes sense ?

I'll make sure to check out this topic quicker next time haha

Thanks again,

Best Regards

lbendlin
Super User
Super User

I have one CALENDAR table, which filters both tables on their record dates, and contains two flags : FG_LAST_MONTH_PRODUCTION and FG_LAST_MONTH_INVOICES, 

why do you need these flags?  The data for these should come from their respecitve fact tables, not from the common calendar table.

Po_
Frequent Visitor

Hi @lbendlin 

 

So sorry, I did not receive a notification for your answer and did not see it sooner ! 

 

Well, there were several reasons to it : 

-First of, ideally your facts tables should only contain IDs and KPIs

-We have a large data model with many (many) fact tables, which need to all be filtered by these flags : having them centered in our calendar table allows to filter them all at once

-Adding a column means adding 20M+ values to each fact tables, whereas having them in a calendar table shrinks it down to roughly 300 rows for a whole model

-Last but not least, this would actually worsen my issue : when filtering directly from your fact tables, you "lose" the rest of your data and are no longer able to retrieve a YTD KPI. In order to get that, you would have to get rid of your calendar table as a whole, and not use it to measure time intelligence, which would be a mistake in a large model.

 

Maybe you had a different data model in mind ? If so do not hesitate to implement it in the data sample file I created, I'd be happy to look further into it if it allows the feature I'm looking for !

Here is the .PBIX file :

https://drive.google.com/file/d/1Dg9f8kB5MYdsve-2LlzaJaViZsXdAQ-i/view?usp=sharing

And, if needed, here is the Excel source file :
https://docs.google.com/spreadsheets/d/184YKuL5VCuhtredD0W8w9nT56otvtvQf/edit?usp=sharing&ouid=11185...

 

Thanks for your reply,

Best regards

There's always a gap of one month between the two

 

I would radically simplify the data model

lbendlin_0-1708012056413.png

 

 

and then use DATEADD(...-1,MONTH).

Po_
Frequent Visitor

I'm sorry maybe there's something I'm missing, how would that help display KPIs for last month of production AND invoice with one slicer ?

Fi, desired result in the new card visualization is :

10M ; 37M ; 10M ; 30M 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors