Month sales and yearly sales based on date selection and relations setting for three master tables

[Anonymous]

Hi team,

           I have been trying to get a logic done,

1) i have a trnsactional date column posting date in Itemledgerentry table, that is the date slicer,from the slicer if a select a date in a month(eg : july 16 2021) i should get thhe sales of the july month(from 1st of july till july 16th 2021).

2) same logic for a year, if a date is slected (eg) 16th july 2021, i should get the sales of the year 2020(from 16th july 2020 till selected date(16th july 2021).

3) If i select a date in the date slicer i should get the entire sales details from the start date of the Database till the selected date.

4) Im also unable to do the connection for the models, it has seven tables, three master tables and three transaction tables,in which two transaction tables "ItemLedgerEntry2021" and "ItemLedegerEntry2020" have the same structure.

5) I removed the duplictaes and nulls, and tried i was able to get many to one relationship,but still the visualization is not with right data,meaning connections are not perfect,

Any help would be great,Attaching the pbix where i have loaded the tables.

Regards

Sarav

[daxer-almighty]

Hi @Anonymous 

I've taken a look at your model... Sorry to say that and I'm not going to beat about the bush but it's soooo bad on sooo many levels. I'm sorry again but I don't want to write measures for it not to encourage you to carry on with it. This model should be changed to a good one, created based on Best Practices. This one will give you problems that you won't even be aware of and I don't want to be part of it.

Very sorry! 😞

[Anonymous]

First and foremost you should learn the principles of dimensional modeling. Please start here: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

Once you've got the model RIGHT can you write any DAX. Write DAX in a bad model and you'll be in deep trouble in no time. Promise.

[Anonymous]

Hi Daxer,

           Thanks a lot for mentioning about the star schema, I fixed the relationship between the tables and I got the data without duplication,just need help regarding the dax concepts that I have mentioned,

 Pls see any help would be great ,

Im attaching the link for my report,in which pls check if my relationships are right and also help for dax would be great, if possible.

Thanks in advance

Sarav

https://deyaar-my.sharepoint.com/:u:/g/personal/saravananv_nationwide_ae/EXPtqKJpR31GgySKqwxZalMBzKt...