Distinct count previous month/week with IF and FIlter Statement on Custom Calender

[Smokey24]

Hi all,

I am using a 5-4-4 calender and in a Matrix I am showing in one column the distinctcount amount of outlets and the column next to it should compare the current amount with the previous month and previous week.

Looks like this:

                 Amount    Prev. Amount 

   Jan            100

(Week 1)      120
(Week 2)        80                   120

(Week 3)        90                     80

   Feb            120                   100

(Week 4)         x                       90
(Week 5)         x                       

(Week 6)         x

To compare the week to the previous week I simply use Time intelligence:

CALCULATE( DISTINCTCOUNT('FactTable Outlets'[FK_Outlet]);

DATEADD( 'MasterCalendar'[Date]; -7; DAY ))

For the months and sum calculations I use this measure:

VAR vActMonth = SELECTEDVALUE('MasterCalendar'[5-4-4 Month number])

VAR vActYear = SELECTEDVALUE('MasterCalendar'[5-4-4 Year])

VAR vMaxMonthNum = CALCULATE(MAX('MasterCalendar'[5-4-4 Month number]); ALL('MasterCalendar'))

VAR vCalc =

       IF(HASONEVALUE('MasterCalendar'[5-4-4 Month number]);

           SUMX(FILTER(ALL('MasterCalendar');

              IF(vActMonth = 1;

                 'MasterCalendar'[5-4-4 Month number] = vMaxMonthNum && 'MasterCalendar'[5-4-4 Year] = vActYear-1;

                 'MasterCalendar'[5-4-4 Month number] = vActMonth-1 && 'MasterCalendar'[5-4-4 Year] = vActYear));

Measure); BLANK())

Now instead of the SUMX I am trying to use the DISTINCTCOUNT so I get the right result for the month row.

Could you help me out?

Thanks!

[amitchandak · Accepted solution]

Please refer to this file to compare the weeks. It uses week Rank. You have to use the same logic even to compare period of 5-4-4 , just name make sure you have something to create Rank

https://www.dropbox.com/s/d9898a48e76wmvl/sales_analytics_weekWise.pbix?dl=0

[Greg_Deckler]

What exactly is the issue?

[Smokey24]

In a matrix I am trying to compare the month to the month before.

One Hierachy level below are the weeks and there I am comparing the week to the week before. Here I just use time intelligence.

But since I am using a 5-4-4 calender I can't use time intelligence to compare the months.

I do have a measure to show me the value of the previous month which looks like this:

VAR vActMonth = SELECTEDVALUE('MasterCalendar'[5-4-4 Month number])

VAR vActYear = SELECTEDVALUE('MasterCalendar'[5-4-4 Year])

VAR vMaxMonthNum = CALCULATE(MAX('MasterCalendar'[5-4-4 Month number]); ALL('MasterCalendar'))

RETURN

     IF(HASONEVALUE('MasterCalendar'[5-4-4 Month number]);

        SUMX(FILTER(ALL('MasterCalendar');

        IF(vActMonth = 1;

       'MasterCalendar'[5-4-4 Month number] = vMaxMonthNum && 'MasterCalendar'[5-4-4 Year] = vActYear-1;

       'MasterCalendar'[5-4-4 Month number] = vActMonth-1 && 'MasterCalendar'[5-4-4 Year] = vActYear));

       Measure); BLANK())

Since I am using DISINCTCOUNT I would need a new logic.

That's why I asked if it is possible to some how use DISTINCTCOUNT instead of SUMX or if there is a way to combine these two.

So I need to DISTINCTCOUNT with a FILTER and an IF statement.