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
Anonymous
Not applicable

Aggregated Running total for YoY/Change

Hi Everyone,

 

I have an Install base (customer count) measure which distinctcounts the number of customer IDs as follows:

edayan_5-1649584043351.png

 

I also have a similiar measure for the same period last year:

edayan_6-1649584099288.png

 

I use the two together to create a measure which reflects the change in customer count:

edayan_7-1649584161273.png

 

 

 

I'm trying to create the same measure but Aggregated but I'm struggling with this and getting contiguous date selections dax-

edayan_0-1649583740568.png

edayan_1-1649538177899.png

Or when i try it this way i get too high numbers:

edayan_4-1649583965160.png

 

So to try to recreate what I have on the right end below, summing the months as a running/aggregate total.

edayan_0-1649537698104.png

 

Thank you everyone,

Eyal

1 ACCEPTED SOLUTION

Hi:

When you do your "Same Period LY measure, can you do it like this:

SPLY Install Base LABRA = CALCULATE([Install Base LABRA], SAMEPERIODLASTYEAR(Dates[Date]))
Then subtract your initial measure from this. I'll call it VAR Install Base LABRA
 
To get cumlative total for the VAR Install Base LABRA
 
= CALCULATE([VAR Install Base LABRA], FILTER(ALLSELECTED(Dates), 
 Dates[Date] <= MAX(Dates[Date])))

Normally this should work. Can you give it a try? Thanks

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

Thanks @Whitewater100 , i'll try changing to my calendar table (i used the detail table date instead) and will update. 

Hello:

Great. I hope that works for you and if it does can you mark as solution? Thanks...

Whitewater100
Solution Sage
Solution Sage

Hi:

I think you may be gettting that message as you may not have a date table, with continuous dates starting before your data starts and ending at end of ear after. When you mark it as a date table and connect it on the one side to your many-side tables, then the built-in time intel functions work great. 

 

I can paste some date table DAX code that you can use as a Date Table if you want to.

 

Dates =

 

-- Specify a start date and end date

VAR StartDate = Date(2010,1,1)

VAR EndDate = Today()

VAR FiscalMonthEnd = 6

 

-- Generate a base table of dates

VAR BaseTable = Calendar(StartDate, EndDate)

 

-- Add the Year for each individual date

VAR Years = ADDCOLUMNS(BaseTable,"Year",YEAR([Date]))

 

-- Add the calendar month and other month related data for each date

VAR Months = ADDCOLUMNS(

    Years,

    "Month",MONTH([Date]),

    "Year and Month Number",FORMAT([Date],"YYYY-MM"),

    "Year and Month Name",FORMAT([Date],"YYYY-MMM"),

    "Fiscal Year", IF( FiscalMonthEnd = 12, YEAR([Date]), IF( MONTH([DATE]) <= FiscalMonthEnd, YEAR([DATE])-1, YEAR([Date]))),

    "Fiscal Month", IF( FiscalMonthEnd = 12, MONTH([Date]),

        IF( MONTH([Date]) <= FiscalMonthEnd, FiscalMonthEnd + MONTH([Date]), MONTH([Date]) - FiscalMonthEnd))

)

 

-- Add the Quarter and other quarter related data for each date   

VAR Quarters = ADDCOLUMNS(

    Months,

    "Quarter",ROUNDUP(MONTH([Date])/3,0),

    "Year and Quarter",[Year] & "-Q" & ROUNDUP(MONTH([Date])/3,0))

 

-- Add the Day and other day related data for each date   

VAR Days = ADDCOLUMNS(

    Quarters,

    "Day",DAY([Date]),

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

    "Day Of Week",WEEKDAY([Date]),

    "Day Of Year", DATEDIFF (DATE(YEAR([Date]),1,1), [Date], DAY) + 1)

 

-- Add the Week (assuming each week starts on a Sunday) and other week related data for each date   

VAR Weeks = ADDCOLUMNS(

    Days,

    "Week Of Month (Sunday)",INT((DAY([Date])-1)/7)+1,

    "Week of Year (Sunday)",WEEKNUM([Date],1),

    "Year and Week (Sunday)",[Year] & "-W" & WEEKNUM([Date],1))

-- Add an 'Is Working Day' column which will be true for all days but Saturday and Sunday.

var WorkingDays = ADDCOLUMNS(

    Weeks,

    "Is Working Day", NOT WEEKDAY( [Date] ) IN {1,7})

 

RETURN WorkingDays

 

I hope this helps!

 

 

Anonymous
Not applicable

Hi @Whitewater100 I tried switiching as follows to my calendar table:

edayan_0-1649842375953.png

 

But I"m still not getting what I need to get (red arrow to yellow)

edayan_1-1649842484425.png

I'm getting the blue..

 

Hi:

When you do your "Same Period LY measure, can you do it like this:

SPLY Install Base LABRA = CALCULATE([Install Base LABRA], SAMEPERIODLASTYEAR(Dates[Date]))
Then subtract your initial measure from this. I'll call it VAR Install Base LABRA
 
To get cumlative total for the VAR Install Base LABRA
 
= CALCULATE([VAR Install Base LABRA], FILTER(ALLSELECTED(Dates), 
 Dates[Date] <= MAX(Dates[Date])))

Normally this should work. Can you give it a try? Thanks

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.