how to calculate pervious 6 month when date column is in text format

[MikeCheng]

Hi all

how to calculate pervious 6 month when date column is text format?

here is the sample data link:

https://docs.google.com/spreadsheets/d/13WJvIqLFjyzkP9cEK4QychWAwjGcio5m/edit?usp=sharing&ouid=11255...

I have these 2 calculations:

previous 6 month sum =

CALCULATE (

    SUM('Raw data'[acture amount]),

    DATESINPERIOD ( 'Raw data'[年月].[Date], MAX ( 'Raw data'[年月].[Date] ), -6, MONTH )

)

actural amount x180 divided by pervious 6 month sum = DIVIDE([acture amountx180], [previous 6 month sum])

I think the result in the below picture shown empty is because the date column is in TEXT format, how do I fix it? please help

I dont want to set the date column to date format because when I drill down, in a Matrix, it will show 3 layer of the table, which is too big

 But when I don't choose the date hierarchy, the table has only 2 layer, but the calculated numbers are wrong.

please help

Best regards,

mike

[srlabhe]

To calculate data for the previous six months in Power BI from a text-formatted date column, first, convert the text column to a date data type in Power Query using a formula like Date.FromText([Your_Text_Column] & "-01"), then create a dedicated date table for robust time intelligence, and finally, build DAX measures using functions like CALCULATE, DATEADD, or DATESINPERIOD to filter your data for the desired prior six-month period. 

1. Convert Text to Date in Power Query 

1. Open Power Query Editor: From the Home tab, select Transform Data to open the Power Query Editor.
2. Select Your Table: In the left pane, click the table containing your text-formatted date column.
3. Add a Custom Column: Go to the Add Column tab and select Custom Column.
4. Enter the Formula: In the Custom Column formula box, enter:

Date.FromText([Your_Text_Column] & "-01") 

• Replace [Your_Text_Column] with the actual name of your text date column.
• Appending "-01" ensures that even if your text only has month and year (e.g., "Jan-2023"), Power BI can create a valid date by defaulting to the first day of the month.

1. Click OK: and then Close & Apply. 
2. Format as Date: In Power BI Desktop, go to the Column Tools tab in the Data view and set the data type of your new column to a Date type. 

2. Create a Date Table 

• It's best practice to create a separate date table and mark it as a date table in Power BI. This table should contain continuous dates and can be created using functions like CalendarAuto().
• Establish a relationship between this date table and your fact table on the date columns.

3. Write DAX Measures

Use DAX (Data Analysis Expressions) to create measures that filter your data for the previous six months. Using DATEADD. 

Code

    Sales Last 6 Months =
    CALCULATE(
        SUM(YourTable[YourSalesColumn]), // Replace with your actual measure/column
        DATEADD('Date'[Date], -6, MONTH)
    )

This formula calculates the sum of your sales, but for the period shifted back by six months from the currently selected date. Using DATESINPERIOD (for a relative range). 

Code

    Sales Previous 6 Months =
    VAR MaxDate = MAX(DateTable[Date]) // Use your Date table's date column
    VAR SixMonthsAgo = DATESINPERIOD(DateTable[Date], MaxDate, -6, MONTH)
    RETURN
        CALCULATE(
            SUM(YourTable[YourSalesColumn]), // Replace with your actual measure/column
            SixMonthsAgo
        )

This measure finds the latest date in the current filter context, generates a table of dates for the previous six months, and then sums your sales within that period. 

[ryan_mayu]

@MikeCheng 

you need to create a date table and try this to create a measure

Measure = if(ISBLANK(sum('Raw data'[acture amount])),blank(),CALCULATE(sum('Raw data'[acture amount]),DATESBETWEEN('Table'[Date],EDATE(max('Table'[Date]),-6),max('Table'[Date]))))

then it will be easier for you to create the second DAX formulat

pls see the attachment below

[MikeCheng]

Hi Ryan

can you explain why does it have to create a date table?

I found out that if I change my Dax to below (just delete .[Date] in my original Dax) and the answer calculated correctly

previous 6 month sum by text =

CALCULATE (

    SUM('Raw data'[acture amount]),

    DATESINPERIOD ( 'Raw data'[年月], MAX ( 'Raw data'[年月].[Date] ), -6, MONTH )

)

*in this case, I don't choose the date hierarchy as well.

can anyone explain this behavier for me?

Best regards,

mike

[v-dineshya · Accepted solution]

Hi @MikeCheng ,

Thank you for the update. Please refer below.

1. When you drag a Date column (real Date type) into visuals or DAX, Power BI automatically creates a hidden “auto date table” this is where [Date], [Year], [MonthNo], [MonthName], etc. 

DATESINPERIOD('Raw data'[年月].[Date], MAX('Raw data'[年月].[Date]), -6, MONTH)

[年月].[Date] refers to hidden auto table, not your column directly. But when your '年月' is text, Power BI does not create this auto table DAX functions like DATESINPERIOD() need a real date column, so they return blank. That’s why originally your measure returned nothing you gave DAX a text column, not a real date column.

2. When you try the below measure

previous 6 month sum by text =
CALCULATE (
SUM('Raw data'[acture amount]),
DATESINPERIOD('Raw data'[年月], MAX('Raw data'[年月]), -6, MONTH)
)

Power BI did not use 'Raw data'[年月] (the text column) directly instead, it internally coerced it into a date type temporarily because the function DATESINPERIOD() can only operate on real dates. If all values are in a recognizable YYYY-MM or YYYY/MM format, Power BI can parse them into real dates, and your DAX starts work even though '年月'’s declared type is Text. It is an implicit type conversion: Text “2023-06” --> Date(2023, 6, 1).

I hope this information helps. Please do let us know if you have any further queries.

Regards,

Dinesh

[v-dineshya]

Hi @MikeCheng ,

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. And, if you have any further query do let us know.

Regards,

Dinesh

[v-dineshya]

Hi @MikeCheng ,

Could you please try the proposed solution shared by  @ryan_mayu ? Let us know if you’re still facing the same issue we’ll be happy to assist you further.

Regards,

Dinesh

[v-dineshya]

Hi @MikeCheng ,

Thank you for reaching out to the Microsoft Community Forum.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Regards,

Dinesh

[v-dineshya]

Hi @MikeCheng ,

We haven’t heard from you on the last response and was just checking back to see, Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Regards,

Dinesh

[v-dineshya]

Hi @MikeCheng ,

We haven’t heard from you on the last response and was just checking back to see, Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Regards,

Dinesh

[v-dineshya]

Hi @MikeCheng ,

We haven’t heard from you on the last response and was just checking back to see, Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot). Do not include sensitive information. Do not include anything that is unrelated to the issue or question. Please show the expected outcome based on the sample data you provided.

Regards,

Dinesh

[mh2587]

Just convert the date into Date Type and only keep the date in matrix you can remove Year and Month etc from the matrix