Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Employee
Employee

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.