Calculating Previous Value by Year, Month and Day

[PBI5851]

Hi,

 I have a matrix table that I have to display the Year , Month and Date in rows and the counts of unique accounts per day as column. To this, i need to add a new column to indicate the up/downswing from previous "calendar". Meaning, for a day, i need to display the up/down swing from previous day. For month, the unique count in that month with the up/down swing from previous month and similar for year. 

I am able to get the values as per below DAX for the days, but the months and years are incorrect, as it seems to be summing up the days, rather than the unique account for that month. And subsequently need to do this when the data increases by year. 

AccessDate	AcctID
9/1/2020	A1001
9/1/2020	B1001
9/1/2020	C1001
9/1/2020	D1001
9/1/2020	E1001
9/2/2020	A1001
9/2/2020	B1001
9/2/2020	C1001
9/2/2020	D1001
9/2/2020	E1001
9/2/2020	F1001
9/3/2020	B1001
9/4/2020	C1001
9/4/2020	D1001
9/5/2020	C1001
9/5/2020	D1001
9/5/2020	E1001
9/5/2020	F1001
9/5/2020	G1001
8/1/2020	A1001
8/1/2020	B1001
8/1/2020	C1001
8/1/2020	D1001
8/1/2020	E1001
8/2/2020	C1001
8/2/2020	D1001
8/2/2020	E1001
8/4/2020	C1001
8/3/2020	D1001
8/3/2020	E1001
8/4/2020	F1001
8/4/2020	G1001
8/5/2020	D1001
8/5/2020	E1001
8/5/2020	F1001
7/1/2020	A1001
7/1/2020	B1001
7/1/2020	C1001
7/1/2020	D1001
7/1/2020	E1001
7/2/2020	B1001
7/2/2020	C1001
7/2/2020	D1001
7/3/2020	A1001
7/3/2020	B1001
7/3/2020	C1001
7/3/2020	D1001
7/3/2020	E1001

Intended Output for month and day is .. 

AccessDate	AcctID Count	Previous Count swing
Sep	7	0
9/5/2020	5	3
9/4/2020	2	1
9/3/2020	1	-5
9/2/2020	6	1
9/1/2020	5	2
Aug	7	2
8/5/2020	3	0
8/4/2020	3	1
8/3/2020	2	-1
8/2/2020	3	-2
8/1/2020	5	2
Jul	5	0
7/5/2020	3	2
7/4/2020	5	0
7/3/2020	5	2
7/2/2020	3	-2
7/1/2020	5	0

So July had 5 unique accounts and Aug had 7 unique so the upswing of 2 for Aug. The individual days within July are correct as they are unique for that day and so on. 

* I'm assuming only 5 days in a month for this example. 8/1/20 is looking at 7/5/20,  and 9/1/20 is looking at 8/5. In full report, 9/1 will be looking at 8/31, and 8/1 will be looking at 7/31 etc for the previous value for the up/down swing.

The DAX i am using are. 

AcctID Count = CALCULATE(DISTINCTCOUNT(Account[AcctID]),USERELATIONSHIP(Date[Date],Account[AccessDate])) 

AcctID Yesterday = CALCULATE(DISTINCTCOUNT(Account[AcctID]),DATEADD(Date[Date],-1,DAY),USERELATIONSHIP(Date[Date],Account[AccessdateDate]))

Previous Count Swing = [AcctID Count]-[AcctID Yesterday]

Please let me know if my request id possible within the same matrix? Or do i have to seperate them into a year and Month and Daily visuals. 

[Icey · Accepted solution]

Hi @PBI5851 ,

How about this:

Previous Count swing =
VAR ThisMonth =
    MONTH ( MAX ( 'Account'[AccessDate] ) )
VAR LastDayofPreviousMonth =
    CALCULATE (
        MAX ( 'Account'[AccessDate] ),
        FILTER (
            ALLSELECTED ( 'Account' ),
            MONTH ( 'Account'[AccessDate] ) = ThisMonth - 1
        )
    )
VAR ValueofLastDayofPreviousMonth =
    CALCULATE (
        [AcctID Count],
        FILTER (
            ALLSELECTED ( 'Account' ),
            'Account'[AccessDate] = LastDayofPreviousMonth
        )
    ) + 0
VAR PreviousCount =
    IF (
        HASONEVALUE ( Account[AccessDate] ),
        CALCULATE (
            [AcctID Count],
            FILTER (
                ALL ( 'Account' ),
                'Account'[AccessDate]
                    = MAX ( 'Account'[AccessDate] ) - 1
            )
        ),
        IF (
            ThisMonth = MONTH ( MINX ( ALLSELECTED ( Account ), Account[AccessDate] ) ),
            0,
            CALCULATE (
                [AcctID Count],
                FILTER (
                    ALLSELECTED ( 'Account' ),
                    MONTH ( 'Account'[AccessDate] ) = ThisMonth - 1
                )
            )
        )
    )
RETURN
    [AcctID Count]
        - IF (
            DAY ( MAX ( Account[AccessDate] ) ) = 1,
            ValueofLastDayofPreviousMonth,
            PreviousCount
        )

Best Regards,

Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@PBI5851 , You can use date table and time intelligence

examples

Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))

This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))
Last Day = CALCULATE(sum('order'[Qty]), previousday('Date'[Date]))

/////month

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

////Year

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below

This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))

Power BI — Year on Year
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — Mon th On Month
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c324...

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions


Appreciate your Kudos.

[PBI5851]

@amitchandak . Thank you. I used your suggestion on the Day behind sales from another ticket.  If i read it correctly, your solutions gives the correct value if looking at just year or month. But can all that be done in a single visual. 

[amitchandak]

@PBI5851 , Are planning to switch these measures. 

Then we have created a measure slicer.

I have done once combined it using is filtered for month and year - https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Last Period Employee, I switched the filter clause, the same thing for a year, month, and day

Other way is measure slicer: https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...

I doubt there is one formula that can do. Unless you one year behind data, this will work with each grouping, day, month and year

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))