Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi there,
Could you please help me figure out how to create a measure that only displays the data from 01/31 up to 09/30 below?
I cannot use a filter in the view because i will be replacing 10/31 value with another. So my main goal is to create another measure
Overall_Total = Running_Total_LastMonth + Oct31_total
Here is the measure i am using
Running_Total_LastMonth =
CALCULATE (
SUM(values),
FILTER(
ALL('Date'[MonthEnd]),
'Date'[MonthEnd] <= MAX('Date'[MonthEnd]) // i was trying to modify this so that it does not display current month data.
)
)
Any help is greatly appreciated.
Thank you
Glen
Solved! Go to Solution.
Hi @gco
Create a measure
Measure = IF ( MAX ( 'calendar'[year] ) = YEAR ( TODAY () ) && MAX ( 'calendar'[month] ) < MONTH ( TODAY () ), CALCULATE ( SUM ( Sheet8[value] ), FILTER ( ALL ( 'calendar' ), 'calendar'[year] = YEAR ( TODAY () ) && 'calendar'[month] < MONTH ( TODAY () ) && 'calendar'[Date] <= MAX ( 'calendar'[Date] ) ) ) )
My calendar table
calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))
add column
monthend = ENDOFMONTH('calendar'[Date])
Hi @gco
Create a measure
Measure = IF ( MAX ( 'calendar'[year] ) = YEAR ( TODAY () ) && MAX ( 'calendar'[month] ) < MONTH ( TODAY () ), CALCULATE ( SUM ( Sheet8[value] ), FILTER ( ALL ( 'calendar' ), 'calendar'[year] = YEAR ( TODAY () ) && 'calendar'[month] < MONTH ( TODAY () ) && 'calendar'[Date] <= MAX ( 'calendar'[Date] ) ) ) )
My calendar table
calendar = ADDCOLUMNS(CALENDARAUTO(),"year",YEAR([Date]),"month",MONTH([Date]))
add column
monthend = ENDOFMONTH('calendar'[Date])
Hi @v-juanli-msft and @Greg_Deckler
Thank you. I am sure i would be able to use your measures in my other project.
I have actually fixed my problem by taking the total of the previous months + current month. I created a calculated column on my previous months table and had used the measure:
Total_Prev_months = CALCULATE (SUM (Prev_Months[Balances]), FILTER (Prev_Months, Prev_Months[IsThisYear] = "YES"))
Total_Current_month = CALCULATE (SUM (Daily_Data[Balances]), FILTER (Daily_Data, Daily_Data[IsCurrentMonth] = "YES"))
Thank you again
Glen
The calculated columns are for the IsThisYear, and IsCurrentMonth.
See if my Time Intelligence the Hard Way provides a different way of accomplishing what you are going for.
https://community.powerbi.com/t5/Quick-Measures-Gallery/Time-Intelligence-quot-The-Hard-Way-quot-TIT...
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
100 | |
54 | |
21 | |
12 | |
11 |