cancel
Showing results for
Did you mean:
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 result

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

Best Regards;

Vick

1 ACCEPTED SOLUTION
Microsoft

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.

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.
6 REPLIES 6
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.
Frequent Visitor

Hi Dale;

here is the link for sample dataset;

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

or

thank you for yor attention on my issue;

warm regards;

Vick

Microsoft

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.

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.
Frequent Visitor

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

hope you can give me any clue to solved this;

thank you for you help

vick

Microsoft

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.
Frequent Visitor

Hi Dale;

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

warm regards;

vick

Announcements