Lookup based upon multiple slicers to provide narrative

[AutyDA]

Hi,

I'm new to power BI and need a bit of help.

I have a set of annual and quartlerly data for several years for various companies. My objective is to allow the user to select a period, a company and yearly / quarterly to filter this and provide text commentary (see below). With smart narrative this works but the results are unreliable and so I want to control the narrative using text boxes.

As shown above I have set up measures using max and min on the dates to identify the start and end dates. What I can't do is use a lookup to get the global revenue on the selected date or identify if it has grown or fallen and by how much by numeric value and percentage.

Any help appreciated.

[v-fenling-msft]

Hi, @AutyDA 

Thanks for Ritaf1983's concern about this issue.

I am glad to help you.

The first thing you need to know is that your Global Revenue is affected by all Slicers together. If you want to return to Global Revenue in 2021, you will need to select the Date Slicer in the 2021 timeframe as well:

Secondly, unfortunately, the Global Revenue you get after selecting a Slicer is not saved, so you can't directly compare the Global Revenue you get for different time periods for the Slicer selection.

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Fen Ling,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[AutyDA · Accepted solution]

Hi,

This was a help. In the end I simplified things and reposted and found a good solution which is posted here... Solved.

Thanks

[AutyDA]

Hi, I have added a link to the pbix file. Once again my aim is obtain the global revenue on the dates specified by the slider, then identify if global revenue has grown or fallen in this period and by how much both numerically and by percentage. I would report all of this in the text box highlighted in yellow.

SampleData.pbix 

[AutyDA]

Unfortunately I'm restricted from uploading any files and so I'll add a data table which you can hopefully copy and paste. This is my source table:

Company No.	Year	Quarter or Year	Date	Operating Margin	Net Profit Margin	Global Revenue	Stock Price
1	2020	Year	12/31/2020	22.61%	4.34%	$               4,395,407	55
1	2021	Year	12/31/2021	6.46%	7.12%	$               3,514,206	44
1	2022	Year	12/31/2022	13.57%	15.83%	$               7,568,492	22
1	2023	Year	12/31/2023	28.92%	10.21%	$               4,331,903	84
1	2024	Year	12/31/2024	25.00%	13.00%	$               5,324,000	74
2	2020	Year	12/31/2020	5.00%	7.00%	$               2,532,567	27
2	2021	Year	12/31/2021	6.00%	8.00%	$             15,000,452	33
2	2022	Year	12/31/2022	5.00%	5.00%	$               2,703,535	36
2	2023	Year	12/31/2023	7.00%	22.00%	$               3,476,524	35
2	2024	Year	12/31/2024	5.00%	5.00%	$               4,232,221	39
3	2020	Year	12/31/2020	4.98%	-48.01%	$             20,705,000	123
3	2021	Year	12/31/2021	7.21%	-1.07%	$               4,300,000	143
3	2022	Year	12/31/2022	9.39%	-2.84%	$               3,550,000	135
3	2023	Year	12/31/2023	10.37%	7.62%	$             25,506,000	164
3	2024	Year	12/31/2024	9.00%	8.00%	$               2,400,000	177
1	2020	Q	12/31/2020	14.28%	9.78%	$               1,010,944	55
1	2021	Q	12/31/2021	11.11%	1.07%	$                   950,000	44
1	2022	Q	12/31/2022	7.56%	6.62%	$               1,892,123	22
1	2023	Q	12/31/2023	10.87%	6.83%	$                   996,338	84
1	2024	Q	12/31/2024	14.00%	7.00%	$               1,224,520	74
2	2020	Q	12/31/2020	3.99%	-20.39%	$                   582,490	27
2	2021	Q	12/31/2021	11.38%	9.53%	$               3,546,852	33
2	2022	Q	12/31/2022	14.80%	7.75%	$                   621,813	36
2	2023	Q	12/31/2023	17.74%	11.46%	$                   800,000	35
2	2024	Q	12/31/2024	13.00%	33.00%	$                   973,411	39

Then I have a Dax Date Table with an active relationship in model view being set up between Date in each table:

DAX DateTable =

ADDCOLUMNS (

    //CALENDAR(DATE(2014,1,1), DATE(2030,12,31)),

    CALENDARAUTO(),

    "Year", YEAR([Date]),

    "Quarter", "Q" & FORMAT(CEILING(MONTH([Date])/3, 1), "#"),

    "Quarter No", CEILING(MONTH([Date])/3, 1),

    "Month No", MONTH([Date]),

    "Month Name", FORMAT([Date], "MMMM"),

    "Month Short Name", FORMAT([Date], "MMM"),

    "Month Short Name Plus Year", FORMAT([Date], "MMM,yy"),

    "DateSort", FORMAT([Date], "yyyyMMdd"),

    "Day Name", FORMAT([Date], "dddd"),

    "Details", FORMAT([Date], "dd-MMM-yyyy"),

    "Day Number", DAY ( [Date] ))

I made 3 slicers for (1) Company No. (2) Quarter or Year and (3) Date using a between slicer. I then created a measure to calculate the start and end dates (StartDate = min('DAX DateTable'[Date]), EndDate = max('DAX DateTable'[Date])) established by the Date slicer.

What I want is if a user selects Company No.1, Year, starting 2021 the global revenue in 2021 for company 1 is returned - i.e.  $ 3,514,206. If the user selects Company No.1, Quarter, starting 2021 the Quarterly global revenue in 2021 for company 1 is returned - i.e. $ 950,000.

I hope that makes sense. Thanks.

[AutyDA]

Here is a sample of my data: 

Company No.	Year	Quarter or Year	Date	Operating Margin	Net Profit Margin	Global Revenue	Stock Price
1	2020	Year	12/31/2020	22.61%	4.34%	$               4,395,407	55
1	2021	Year	12/31/2021	6.46%	7.12%	$               3,514,206	44
1	2022	Year	12/31/2022	13.57%	15.83%	$               7,568,492	22
1	2023	Year	12/31/2023	28.92%	10.21%	$               4,331,903	84
1	2024	Year	12/31/2024	25.00%	13.00%	$               5,324,000	74
2	2020	Year	12/31/2020	5.00%	7.00%	$               2,532,567	27
2	2021	Year	12/31/2021	6.00%	8.00%	$             15,000,452	33
2	2022	Year	12/31/2022	5.00%	5.00%	$               2,703,535	36
2	2023	Year	12/31/2023	7.00%	22.00%	$               3,476,524	35
2	2024	Year	12/31/2024	5.00%	5.00%	$               4,232,221	39
3	2020	Year	12/31/2020	4.98%	-48.01%	$             20,705,000	123
3	2021	Year	12/31/2021	7.21%	-1.07%	$               4,300,000	143
3	2022	Year	12/31/2022	9.39%	-2.84%	$               3,550,000	135
3	2023	Year	12/31/2023	10.37%	7.62%	$             25,506,000	164
3	2024	Year	12/31/2024	9.00%	8.00%	$               2,400,000	177
1	2020	Q	12/31/2020	14.28%	9.78%	$               1,010,944	55
1	2021	Q	12/31/2021	11.11%	1.07%	$                   950,000	44
1	2022	Q	12/31/2022	7.56%	6.62%	$               1,892,123	22
1	2023	Q	12/31/2023	10.87%	6.83%	$                   996,338	84
1	2024	Q	12/31/2024	14.00%	7.00%	$               1,224,520	74
2	2020	Q	12/31/2020	3.99%	-20.39%	$                   582,490	27
2	2021	Q	12/31/2021	11.38%	9.53%	$               3,546,852	33
2	2022	Q	12/31/2022	14.80%	7.75%	$                   621,813	36
2	2023	Q	12/31/2023	17.74%	11.46%	$                   800,000	35
2	2024	Q	12/31/2024	13.00%	33.00%	$                   973,411	39

And for my date table I'm using the following DX Date Table.

DAX DateTable =

ADDCOLUMNS (

    //CALENDAR(DATE(2014,1,1), DATE(2030,12,31)),

    CALENDARAUTO(),

    "Year", YEAR([Date]),

    "Quarter", "Q" & FORMAT(CEILING(MONTH([Date])/3, 1), "#"),

    "Quarter No", CEILING(MONTH([Date])/3, 1),

    "Month No", MONTH([Date]),

    "Month Name", FORMAT([Date], "MMMM"),

    "Month Short Name", FORMAT([Date], "MMM"),

    "Month Short Name Plus Year", FORMAT([Date], "MMM,yy"),

    "DateSort", FORMAT([Date], "yyyyMMdd"),

    "Day Name", FORMAT([Date], "dddd"),

    "Details", FORMAT([Date], "dd-MMM-yyyy"),

    "Day Number", DAY ( [Date] ))

The two measures I added to this are 

StartDate = Min ('DAX DateTable'[Date])

EndDate = Max ('DAX DateTable'[Date])

I hoope this helps.

[Ritaf1983]

Hi @AutyDA 

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