Problem with month to month calculation quick formula Error message

[Mstrande]

Hi,

Realised that my problem description was bad so here is an update:

I want to use the quick formula month to month difference formula. Then I get this error in the formula:

Then I get this error:

Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column.

When I try to mark my Table as a date table I get this error:

The date columns need unique values

The date column cannot have gaps in date

I found a solution . I found below but dont understand: :

https://community.powerbi.com/t5/Desktop/How-to-fill-the-gaps-in-values-between-dates/td-p/395510

In my data I only have one date in each month. I mean by project and organisation we only have data per month. I have monthly data:

Result date	Project	MSEK
den 15 Juni 2016	A	5
den 15 Januari 2016	A	6
den 15 Febryary 2016	A	4
den 15 Mars 2016	A	5

Do I need data for every day or what?

Mstrande

[v-eachen-msft]

Hi @Mstrande ,

You could create a rank column based on your date column at first. Then refer to the following measure to get the result.

Column 2 =
VAR a =
    CALCULATE (
        FIRSTNONBLANK ( 'Table'[MSEK], 1 ),
        FILTER (
            'Table',
            'Table'[rank]
                = EARLIER ( 'Table'[rank] ) - 1
                && 'Table'[Project] = EARLIER ( 'Table'[Project] )
        )
    )
RETURN
    'Table'[MSEK] - a

Here is my test file for your reference.

[Mstrande]

Hi @v-eachen-msft @amitchandak !

the quick formula only supports unique date values and I have several MSEK rows on the same date. 

Date              Project        costtype      MSEK

20200515     Chair            Manhour      5

20200515     Chair            Testobject    10

20200415     Chair          Manhour      5

20200515     Chair            Testobject    10

MSTRANDE

[amitchandak]

@Mstrande , Not sure I am clear with your issue. But make sure you have created a date table and join it with your date in your transaction table

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-Y...


Appreciate your Kudos.

[Mstrande]

@amitchandak 

Thanks for input but please see updated problem description

[amitchandak]

@Mstrande , the column in your table, is that detected as date, if no, make sure it does. Then create a date table. And use that for time intelligence. 

Create the date table using the calendar function

[Mstrande]

@amitchandakthanks for reply; The column is marked as a date. With format "den d MMMM YYYY" Please look a solution above, how does that work?

[amitchandak]

@Mstrande , Can you share sample data(with more rows) and sample output in table format? Or a sample pbix after removing sensitive data.