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
thliberato
Helper I
Helper I

TOPN current month x quantity Tickets Last Month

Good Morning. I need help.

 

I Have one fact table named F_incidents.
I created 3 measure listed below:

 

-Qtde Raised sem vazio= CALCULATE(COUNTROWS(F_Incidentes);F_Incidentes[CATEGORIA_KPI]="Raised";F_Incidentes[COD_SEQ_SISTEMA]>0)

 

Raised TopN = if(HASONEVALUE(dTOPN[TOPN]);if(rankx(all(D_Sistema[SISTEMA]);[Qtde Raised sem vazio])<=values(dTOPN[TOPN]);[Qtde Raised sem vazio];blank()))

 

Rank = rankx(all(D_Sistema[SISTEMA]);[Qtde Raised sem vazio])

 

With these mesuares I created the [TOP5 System (ticket number) x November (Selected in calendar filter)] Bar Graph.

This graph shows top5 Systems with the bigest quantity of tickets per month (I selected the month in a filter). (Like first grah in a example.

 

Now, I need your help to include in the same bar graph an additional information (Column):

Quantity of tickets considering the previous month considering that one selected in a filter.

 

Example.

I Choose in a calendar filter: November.

 

First graph shows top5 system x November.

TOP5.JPG

 

The second graph need shows top5 System (november) x Quantity tickets previous month (October ins this example).

 

 

1 ACCEPTED SOLUTION
bourquejeff
Frequent Visitor

I've solved your problem using the PREVIOUSMONTH function in DAX. I have a workbook I can send you with the solution if you have an email address I can send it to.

 

In short:

 

Make sure you have a calendar table where the date field is data type: "Date" and you also have a date field with data type: "Date" on your TicketSales table. 

 

For TOPN, you can just use the new filter in Power BI: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-feature-summary/preview/#topN

 

Then, add this measure (edited with your specific information):

 

TicketsLastMonth = CALCULATE(SUM(TicketSales[Ticket Sales]), PREVIOUSMONTH('Calendar'[Date]))

 

Add the Month slicer from your Calendar table, then add a Clustered Column Chart with your System on Axis, and TicketSales and TicketsLastMonth on Values.

 

 

View solution in original post

1 REPLY 1
bourquejeff
Frequent Visitor

I've solved your problem using the PREVIOUSMONTH function in DAX. I have a workbook I can send you with the solution if you have an email address I can send it to.

 

In short:

 

Make sure you have a calendar table where the date field is data type: "Date" and you also have a date field with data type: "Date" on your TicketSales table. 

 

For TOPN, you can just use the new filter in Power BI: https://powerbi.microsoft.com/en-us/blog/power-bi-desktop-october-feature-summary/preview/#topN

 

Then, add this measure (edited with your specific information):

 

TicketsLastMonth = CALCULATE(SUM(TicketSales[Ticket Sales]), PREVIOUSMONTH('Calendar'[Date]))

 

Add the Month slicer from your Calendar table, then add a Clustered Column Chart with your System on Axis, and TicketSales and TicketsLastMonth on Values.

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors