Getting the same results in every row while calculating TotalYTD

[Anonymous]

I am getting the same results in every row while calculating the TotalYTD in powerBI. I have a fact table and dimensional date table. My DAX formula is very simple as TotalYTD(sum(FactTable(sales), DimDate(Date)). it gives the same result in every row when use the fields from DimDate table and also fills the empty rows with same results. Your help will be highly appreciated.

[Anonymous]

Thanks community, the DAX function in fine and working now. i have been pulling my hair since yesterday to solve this simple puzzle but it is fixed now. i do not know what was happened. the same formula started working automatically giving the correct results. i had no idea what was happened. 

[Radhika2605]

@Anonymous Can you please share screenshot of your model?

It appears that your model is not set up correctly which could be causing issues. 

1. Check if there is an "Active" relationship between the tables.

2. Also, this thing might not make much difference but could you please go into your data model and right click on the dat table and click on. - mark as date table and choose the date column. Sometimes when date table is not marked as a date table then there are some issues in time intelligence functions. 

3. Please share a screenshot of date table as well as the other table so that the fields in it are visible to understand better.

4. Please share a screenshot of your model.

Thanks

Radhika

[Anonymous]

It is the data model i am using. it gives me the same result in every field. 

i am trying to use the following DAX function and getting the same results in every field

[Ashish_Mathur]

Hi,

Remove the DateTable[DayofWeek]=2 input from your measure.

[Anonymous]

Thanks @Ashish_Mathur  for being here again. i removed that filter but now it is giving me the yTD results in every field. i wonder why is that giving me same value in every field. also i am learing and trying to get the YTD sale for all 2nd day of week which gives me a specific value agianst every record as i have shared in image.

[Ashish_Mathur]

Hi,

I am totally confused now.  Share the link from where i can download your PBI file and show the expected result very clearly.

[Anonymous]

https://gofile.io/d/mttgaz

kindly have a look. i want TotalYTD sales of weekdays

[VahidDM · Accepted solution]

Hi @Anonymous 

Try this measure:

Measure YTD = 
CALCULATE (
    SUM ( Actuals[Actual] ),
    FILTER (
        ALL ( DateTable ),
        DateTable[DayOfWeek] <= MAX ( DateTable[DayOfWeek] )
    )
)

Output:

Link to download updated file: https://gofile.io/d/ClzBtt

Did I answer your question? Mark my post as a solution!

Appreciate your Kudos  !!

[daxer-almighty]

@VahidDM, @Anonymous 

Sadly, the measure by @VahidDM is nothing like YTD. It's a version that works OK only, ONLY, if the axis has the day of week on it. Try to put anything else on the axis and you'll get some totally incomprehensible numbers.

[daxer-almighty]

Please do not re-invent the wheel. There are so many excellent resources that teach all there is to know about how to properly build models and calculations... Here's one that I've found within several seconds: https://youtu.be/zYIxukD2KCM

[VahidDM]

Hi @Anonymous 

Could you please share a snapshot from your report here (scrrenshot from able you used that measure)? Or share your desktop version file with (*.pbix) format on gofile.io and send a link here 

Appreciate your Kudos  !!

[Ashish_Mathur]

Hi,

There should be a relationship (Many to One and Single) from the Date column of your FactTable to the Date column of the DimDate table.  To your visual, drag the Year/Month/Date from the DimDate table.

[Anonymous]

Thaks for your reply. yes there is a one to many relation between dimdate table and fact table. i can also see this in my model but do not know why does it give the wrong calculation or the same in every row. 

[Ashish_Mathur]

Share the link from where i can download your PBI file.

[Anonymous]

https://app.powerbi.com/groups/me/datasets/c19103eb-51ee-42c9-ab08-43539b93cfee/details?ctid=ee903dc...

Please use this link. i am a new BI suer so do not know enough.

Thanks for getting me back again

[Ashish_Mathur]

Hi,

That link takes me to a sign-in page.  Share the link from where i can download your PBI file.