cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
vicks
Frequent Visitor

YTD and YTD last year calculation from new measure(divide 2 column) not working

Hi

 

I have food dataset with dim_date relationship, i have food cost, food revenue measures, and i have some calculated measures in a table;

for food cost, i have

1. A_Cost Food Ttl = SUM('fact_ed_gohr'[A_Cost Food])

2.  Food LY = CALCULATE('fact_ed_gohr'[A_Cost Food Ttl],SAMEPERIODLASTYEAR('dim_Date'[date]))

3. A_Cost Food YTD = TOTALYTD('fact_ed_gohr'[A_Cost Food Ttl],dim_Date[date])

4. A_Cost Food YTD LY = TOTALYTD('fact_ed_gohr'[A_Cost Food Ttl],SAMEPERIODLASTYEAR((dim_Date[date])))

everything looks fine

 

for food revenue, i have

1.A_Food Revenue Ttl = SUM('fact_ed_gohr'[A_Food Revenue])*-1

2. Revenue LY = CALCULATE('fact_ed_gohr'[A_Food Revenue Ttl],SAMEPERIODLASTYEAR('dim_Date'[date]))

3.A_Food Revenue YTD = TOTALYTD('fact_ed_gohr'[A_Food Revenue Ttl],'dim_Date'[date])

4.A_Food Revenue YTD LY = TOTALYTD('fact_ed_gohr'[A_Food Revenue LY],'dim_Date'[date])

and also the result was fine;

 

but then i need  profitability percentage  which i have to divide food cost and food revenue, 

1.A_Food Profitability Ttl = divide('fact_ed_gohr'[A_Cost Food Ttl] , 'fact_ed_gohr'[A_Food Revenue Ttl],0)

2. A_Food Profitability LY = CALCULATE([A_Food Profitability Ttl],SAMEPERIODLASTYEAR('dim_Date'[date]))

so far the result was fine, but when i need YTD for profitability the result was "0"

here is the formula

A_Food Profitability YTD = TOTALYTD('fact_ed_gohr'[A_Food Profitability Ttl],dim_Date[date])

A_Food Profitability YTD LY :=TOTALYTD('fact_ed_gohr'[A_Food Profitability LY],dim_Date[date])

 

i attached the result in power bi

YTD Profitability was wrong resultYTD Profitability was wrong result

what formula should i used to calculated YTD  and YTD LY on that case;

 

Best Regards;

 

Vick

1 ACCEPTED SOLUTION

Hi Vick,

 

I copied all your formulas. No issue at all. Please check out it in the attachment. Please also change the format as the snapshot shows.

YTD_and_YTD_last_year_calculation_from_new_measure_divide_2_column_not_working

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

6 REPLIES 6
v-jiascu-msft
Microsoft
Microsoft

Hi Vick,

 

It seems no errors in your formulas. Please add more digital points to the result to see if it will show something. Can you share the file? You can mask your data first.

 

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dale;

 

here is the link for sample dataset;

 

https://1drv.ms/x/s!AjuODxf7323-aUatt5spFxn-DlY

or

https://drive.google.com/drive/folders/1FdBtbEr1je5NzEN7rCKzPRoDmM56YXTI

 

thank you for yor attention on my issue;

 

warm regards;

 

Vick

Hi Vick,

 

I copied all your formulas. No issue at all. Please check out it in the attachment. Please also change the format as the snapshot shows.

YTD_and_YTD_last_year_calculation_from_new_measure_divide_2_column_not_working

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dale;

 

I've test the DAX Formula, i think i used the wrong formula for Profitability YTD and Profitability YTD LY, i tried your DAX formula for YTD and it worked; 

A_Food Profitability YTD = TOTALYTD(sumx(SUMMARIZE(fact_ed_gohr, dim_Date[Date].[Year], dim_Date[Date].[Month], "dd", 'fact_ed_gohr'[A_Food Profitability Ttl]), [dd]),dim_Date[date])

 

i get the point that i should be summarized/group by the data, but how the logic for YTD LY; i tried this

A_Food Profitability YTD LY = TOTALYTD(sumx(SUMMARIZE(fact_ed_gohr, dim_Date[Date].[Year], dim_Date[Date].[Month], "aa", 'fact_ed_gohr'[A_Food Profitability LY]), [aa]),dim_Date[date])

the result is blank 

 

and of course when i use my old formula, the result is wrong

A_Food Profitability YTD LY old = TOTALYTD('fact_ed_gohr'[A_Food Profitability LY],dim_Date[date])

here is the attachment

 

 

 Power bi YTD LY issue.png

 

hope you can give me any clue to solved this;

 

thank you for you help 

 

vick

Hi Vick,

 

The context of SUMMARIZE is relatively independent. We can change it like below.

A_Food Profitability YTD LY new =
CALCULATE (
    [A_Food Profitability YTD],
    SAMEPERIODLASTYEAR ( 'dim_Date'[Date] )
)

Just in case the [A_Food Profitability YTD] is as follows. You can rename the blue part to another name at the same time.

A_Food Profitability YTD =
TOTALYTD (
    SUMX (
        SUMMARIZE (
            fact_ed_gohr,
            dim_Date[Date].[Year],
            dim_Date[Date].[Month],
            "total", 'fact_ed_gohr'[A_Food Profitability Ttl]
        ),
        [total]
    ),
    dim_Date[date]
)

Best Regards,

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Dale;

 

Yes, I resolved this issue, now everything looks fine, thank you for your help;

 

warm regards;

 

vick

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!