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
manojsv19
Regular Visitor

DAX measure to show the rolling total for each month based on column values in a matrix visual

Hello All,

 

I have a requiremet to show the count of old meter and new meter in a single matrix. Old meter count will be decreasing  and new meter count will be increasing on daily basis.  I am using direct query and not storing any data inside Power BI. Data from table itself coming as rolling total on daily basis (Sample data from the table provided below).

 

I wanted to display the min of old count and max of new count on monthly basis as shown below. Example for the month of october in matrxi, Count of old meter is 9000 and new meter is 2000. In november, Old meter is 5000 and new meter is 6000. Expected output is provided below.

 

I am facing challenges in creating a DAX measure to show the old and new count as metioned in matrix visual. I would appreciate any suggestions/input in showing the old and new count in a matrix.

 

Data from Input table

DateOld Meter CountNew Meter Count
10/20/2019100001000
10/21/201990002000
11/5/201985002500
11/10/201975003500
11/12/201950006000
12/1/201940007000
12/15/201920009000
12/20/2019100010000

 

 

Expected Output

MetersOctNov Dec
Old Meter Count900050001000
New Meter Count2000600010000

 

2 REPLIES 2
az38
Community Champion
Community Champion

Hi @manojsv19 

try new table

NewTable = summarize('Table1';Table1[Date].[Month];"Old Meter Count";min('Table1'[Old Meter Count]);"New Meter Count";MAX('Table1'[New Meter Count]))

are sure you need output exactly as you showed above? it will not be so easy 🙂

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn


do not hesitate to give a kudo to useful posts and mark solutions as solution
LinkedIn

Hi,

 

Thank you for the inputs!!

 

Yes, my requirement is to show the output as provided in the example and am using direct query. I wanted to get the summarize count from the query and don't want to do the aggregration at Power BI.

 

Let me konw for any additional information.

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.