Cumulative total issue

Power BI
1

I have a DAX to calculate cumulative total. it is giving correct result if there are no filters or slicers applied. I have year slicer, when I click on that, 2019 year gives me correct result for every month but when I click 2018, it is giving me same result for every month. I have only data from September to December for 2018.
Formula:

Cumulative District Logins = IF([Total District Logins]= 0, BLANK(),
                                    CALCULATE([Total District Logins] ,
                                        FILTER(ALLSELECTED(Ed360Usage[Logged in date].[Date]),
                                               Ed360Usage[Logged in date].[Date]<=MAX(Ed360Usage[Logged in date].[Date])))  )

2019 Data:
Cumulative%202019

2018 data
Cumulative%202018

I do not have date table. I did try using data table but Date table would not connect properly to my fact table. None of the fact table dates matching my Date table. I’m not able to figure out why:

Date%20Table%20relationships
Date Table relationships.png674×576 21.1 KB

Dates%20not%20matching%20any%20Fact%20tabledate

2

@pshetty,

Welcome to the forum!

If you create a date table per the instructions below that spans at least the min and max dates of your fact table, mark it as a date table and then create the proper 1:many relationship between the date table and the fact table, I think that will solve your problem.

http://portal.enterprisedna.co/courses/303649/lectures/4673891

  • Brian
1 Like
3

Thank you for the suggestions. I will try this. Thanks for the time.

4

@pshetty,

Glad to help. Just give a shout if you have any problems after trying that.

  • Brian
5

Hi @pshetty

Welcome to the forum.

The possible issues could be date format not similar in both the tables, or your date table contains dates ( example 1/1/2018 to 12/1/2018) and your data is not present for these dates.
Please expand your dates table to match with your data-set.
Else refer to below video for more clarity.

1 Like
6

Date table and fact table dates have same format. please find attached screenshot. Still it is not matching when I put them into one table:

Dates in Separate table visual:
Date%20table%20vs%20fact%20dates

Dates in same table visual:
Date%20table%20vs%20fact%20dates%20in%20same%20table%20view

Thanks for the video. I’m going to try all of the suggestions today to figure this out.

7

Cool do try it. If you still face any issue, then please share pbix file and I can look into it.

8

Thank you @BrianJ and @kkrj.ankit . Both of your suggestions combined helped me figure out this. The issues I had with date was, my fact table date- data type should have been changed to “Date” instead of “Date Time” at the time of Transfer Data. I changed Data type after loading data. Apparently it does not work. Also, In Cumulative calculation, I was referring Month instead of date. Month of a date does not work. it has to be date field as we are calculating based on date. Lot of lessons learnt. Thank you.

2 Likes