cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
anithaMallam
Helper III
Helper III

Need Help

Hi All,

             Could someone please help me on the  below.

i need to calculate the below measures as shown in screenshot.

anithaMallam_0-1631784021596.png

i created the measures as below.

1.Actual YTD =
var val= TOTALYTD(SUMX('InfoMart CostOut_newtest','InfoMart CostOut_newtest'[Value]),'InfoMart CostOut_newtest'[DateKey])
RETURN
val

2.Target YTD=
var val= TOTALYTD(SUMX('InfoMart Costout_Target2021','InfoMart Costout_Target2021'[Target]),''InfoMart Costout_Target2021'[DateKey])
RETURN
val

3.FY target=
SUMX (
FILTER ( 'InfoMart Costout_Target2021', RELATED ( 'InfoMart Costout_Target2021'[DateKey] ) ),
'InfoMart Costout_Target2021'[Target]

could someone please let me know are these measures created are correct and if not could some one of you please correct them.

 

Regards

Anitham

 

4 REPLIES 4
v-yalanwu-msft
Community Support
Community Support

Hi, @anithaMallam ;

Is your problem solved?? If so, Would you mind accept the helpful replies as solutions? Then we are able to close the thread. More people who have the same requirement will find the solution quickly and benefit here. Thank you.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

v-yalanwu-msft
Community Support
Community Support

Hi, @anithaMallam ;

You could try it.

Actual YTD =
CALCULATE (
    SUM ( 'InfoMart CostOut_newtest'[Value] ),
    FILTER ( ALL ( 'InfoMart CostOut_newtest' ), [month] <= 6 )
)
Target YTD =
VAR val =
    CALCULATE (
        SUM ( 'InfoMart Costout_Target2021'[Target] ),
        FILTER ( ALL ( 'InfoMart Costout_Target2021' ), [month] <= 6 )
    )
RETURN
    val
Target YTD =
VAR val =
    CALCULATE (
        SUM ( 'InfoMart Costout_Target2021'[Target] ),
        ALL ( 'InfoMart Costout_Target2021' )
    )
RETURN
    val

Because it's not clear what your actual table data looks like? , so not sure if your formula is correct, and the [DateKey] in your expression?
Or based on the simple sample data you provide, provide the result you want to output.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Greg_Deckler
Super User
Super User

@anithaMallam Very hard to say given the information provided. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

 

I can say that I am not exactly a fan of TI functions. You may find this helpful - https://community.powerbi.com/t5/Community-Blog/To-bleep-With-Time-Intelligence/ba-p/1260000

Also, 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...


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...

Hi Greg,

               sample data which looks as bleow

PREVIEW
these 2 tables which i have to use to create those calculations

Actual Table data

yearperiodmonthunitvalue1value2total
2021jan1A02.182.18
2021feb2B4.7111717.2418921.95306
2021march3C0.900.9
2021april4D000

 

Target Data Table

yearmonthunitctypevalue1value2
20211APCSG&A00
20211ENNSG&A00
20211BCPSG&A00

 

Regards

Anitham

Helpful resources

Announcements
Power BI Show Episode 10 Recap

The Power BI Community Show

Watch the playback when Amit Chandak, a Power BI Super User, demos how to use Field Parameters to make reports more dynamic.

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Health and Life Sciences Power BI User Group

Health and Life Sciences Power BI User Group

Power BI specialists at Microsoft have created a community user group where customers in the provider, payor, pharma, health solutions, and life science industries can collaborate.

Ignite 2022

What's Next at Microsoft Ignite 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Top Solution Authors