Time intelligence issues

[Anonymous]

Hi

I'm new to Power BI but very familiar with both Tableau and Qlik Sense.

I want to make a distinct count of a logid both last month and three months back, thus I can calculate the change over a one month period and a three month period. 

I have tried to do it in the two following ways, but both measures return a blank output. Can anyone tell me what I'm doing wrong? Note that 'Calendar' is a date table and is realted to the TableA by the date.

 
Test1 = CALCULATE(
DISTINCTCOUNT(TableA[logid]),
PREVIOUSMONTH('Calendar'[Date])
)

Test2 =

VAR LastMonth = MAX('Calendar'[monthKey])-1

RETURN

CALCULATE(

DISTINCTCOUNT(TableA[logid]),

FILTER('Calendar', 'Calendar'[monthKey]=LastMonth)

)

[AntrikshSharma · Accepted solution]

@Anonymous  Use ALL for removing active filters from the Calendar table.

Test2 =
VAR LastMonth =
    MAX ( 'Calendar'[monthKey] ) - 1
RETURN
    CALCULATE (
        DISTINCTCOUNT ( TableA[logid] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[monthKey] = LastMonth
        )
    )

[AntrikshSharma · Accepted solution]

@Anonymous  Use ALL for removing active filters from the Calendar table.

Test2 =
VAR LastMonth =
    MAX ( 'Calendar'[monthKey] ) - 1
RETURN
    CALCULATE (
        DISTINCTCOUNT ( TableA[logid] ),
        FILTER (
            ALL ( 'Calendar' ),
            'Calendar'[monthKey] = LastMonth
        )
    )

[Anonymous]

It worked - thank you very much.

@AntrikshSharma Can you please explain me why?

[AntrikshSharma · Accepted solution]

Let's say you are currently looking at March, for march what all dates are available in the current filter context? all the days of the march, if you want to go back and forward in time you need to have all the dates in the current filter context, now how do you get whole of the dates table in each cell of a visual? you use ALL it return whole date table ignoring any filter coming from any where in the report, now you can use this to move back or forward in dates. so When you are at March, you will have whole Calendar containg all the years that you have defined in the calendar table, here is an example of running total.

When I am at 1/1/2007, what is the max date? it is 1/1/2007, when I am at 1/10/2007 then what is the max date? it is 1/10/2007, when I am at Month level of January, what is the max date? it is 1/31/2007, now when we use ALL we can use this MAX function to get all the dates before the MAX date from our report.

but in case if we weren't using ALL, at the day level we would always get one row from the calendar table, but in order to get all the dates less that 10th I need to have a table that returns all the dates so that I can define my MIN and MAX or any other date operation.

With each increment in dates, FILTER gets the list of all the dates that are less than the date of the current cell in the visual and with this it is able to do a running total from the begining.

Running Total = 
VAR MaxDateInFilterContext = MAX ( Dates[Date] ) -- The max date from the current cell in the visual
VAR MaxYear = YEAR ( MaxDateInFilterContext ) -- Year of the max date to reset the running total next year
VAR DatesLessThanMaxDate =
    FILTER (
        ALL ( Dates[Date], Dates[Calendar Year Number] ),
        Dates[Date] <= MaxDateInFilterContext -- Get all the dates that are less than the max dates
            && Dates[Calendar Year Number] = MaxYear -- For the current year
    ) -- List of dates that are less than 1/11 just an example
VAR Result =
    CALCULATE ( [Total Sales], DatesLessThanMaxDate ) --push all the list of dates into the filter context and                                                               evaluate the measure
RETURN
    Result

[amitchandak]

@Anonymous ,you need to use date table for time intelligence

Example

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))))

Refer :

Power BI — YTD
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
Power BI — QTD
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
Power BI — MTD
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
Power BI — WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
Power BI — Day Intelligence
https://medium.com/@amitchandak.1978/power-bi-day-intelligence-questions-time-intelligence-5-5-5c3243d1f9

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
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

[Anonymous]

@amitchandak thanks for all links! But I've already created a date table like this:

DATE =

VAR MINYEAR = YEAR ( MIN ( Publix_AMS[reportedtime] ) )

VAR MAXYEAR = YEAR ( MAX ( Publix_AMS[reportedtime] ) )

RETURN

ADDCOLUMNS (

FILTER (

CALENDARAUTO( ),

AND ( YEAR ( [DATE] ) >= MINYEAR, YEAR ( [DATE] ) <= MAXYEAR )

),

"CALENDAR YEAR", "CY " & YEAR ( [DATE] ),

"MONTH NAME", FORMAT ( [DATE], "MMMM" ),

"MONTH NUMBER", MONTH ( [DATE] ),

"WEEKDAY", FORMAT ( [DATE], "DDDD" ),

"WEEKDAY NUMBER", WEEKDAY( [DATE] ),

"QUARTER", "Q" & TRUNC ( ( MONTH ( [DATE] ) - 1 ) / 3 ) + 1

)

[Fowmy]

@Anonymous 

Hope you are applying your measure in a visual like table with a column that has the month. You referring to the current month here so it has to be able to identify the month.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon on the right if you like this reply 🙂

YouTube, LinkedIn 

[Anonymous]

Yes, of course.

When I try to do using quick measure I get the error: "Only Power BI-provided date hierarchies or primary date columns are supported."