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
ManjushVG
New Member

undefined

Hi all, I am having matrix view in which I am having "Idea Title" column in Rows, "Month" from calculated date column is used in column and "Cumulative Total Planned Savings (USD)" in values as show in image below.

 

ManjushVG_1-1716175610808.png

Cumulative Total Planned Savings (USD) =

VAR CurrentDate = TODAY()
VAR CurrentYear = YEAR(CurrentDate)
RETURN
    SUMX(
        'Initiative Tracker',
        CALCULATE(
            [Total Planned Savings (USD)],
            FILTER(
                ALL('Calendar Date'),
                YEAR('Calendar Date'[Date]) = CurrentYear
            )
        )
    )
 
Here [Total Planned Savings (USD)] is also a measure which is
[Total Planned Savings (USD)] = 
DIVIDE(SUM('Initiative Tracker'[Total Planned Savings (USD)]),SUM('Initiative Tracker'[Planned Saving Months]))
 
Let's go to above image, I am seeing the values 13490 for may. I have "Planned start date" column. Here 13490 is showing may beacuse the "Planned start date" has May date. My requirement is to show the same value 13490 for all the future months from May to december. This is for all the below rows as well. Based on the month of "Planned start date" I need to show the [Cumulative Total Planned Savings (USD)] to all future months of month in "Planned start date". Thanks in advance!
1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@ManjushVG, Try like

 

Cumulative Total Planned Savings (USD) =
VAR CurrentDate = TODAY()
VAR CurrentYear = Max('Calendar Date'[Date])
RETURN
SUMX(
'Initiative Tracker',
CALCULATE(
[Total Planned Savings (USD)],
FILTER(
ALL('Calendar Date'),
YEAR('Calendar Date'[Date]) = CurrentYear
)
)
)

 

 

or you can try like

 

Cumulative Total Planned Savings (USD) =
VAR CurrentDate = TODAY()
VAR CurrentYear = Max('Calendar Date'[Date])
RETURN
SUMX(
'Initiative Tracker',
CALCULATE(
[Total Planned Savings (USD)],
FILTER(
ALL('Calendar Date'),
YEAR('Calendar Date'[Date]) = today()
)
)
)

 

View solution in original post

1 REPLY 1
amitchandak
Super User
Super User

@ManjushVG, Try like

 

Cumulative Total Planned Savings (USD) =
VAR CurrentDate = TODAY()
VAR CurrentYear = Max('Calendar Date'[Date])
RETURN
SUMX(
'Initiative Tracker',
CALCULATE(
[Total Planned Savings (USD)],
FILTER(
ALL('Calendar Date'),
YEAR('Calendar Date'[Date]) = CurrentYear
)
)
)

 

 

or you can try like

 

Cumulative Total Planned Savings (USD) =
VAR CurrentDate = TODAY()
VAR CurrentYear = Max('Calendar Date'[Date])
RETURN
SUMX(
'Initiative Tracker',
CALCULATE(
[Total Planned Savings (USD)],
FILTER(
ALL('Calendar Date'),
YEAR('Calendar Date'[Date]) = today()
)
)
)

 

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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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