Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Sean-OReilly
Helper IV
Helper IV

Can't Get DatesinPeriod to Work properly

Hi

I'm using the below DAX 

Costs last 2 months = calculate ( [Total Costs to Date], DATESINPERIOD(MK_Job_LedgerEntries[Posting_Date], max(MK_Job_LedgerEntries[Posting_Date]), -2, MONTH ))
 
My issue is that when I apply the measure against a Table that also has the Job Number it is using the Max Date of the posting against that Job and working back 2 months from there. I want it to calculate the Max date based on Today's date, not the most recent posting date against that job.
For example, if the last cost posted against a Job was 31/12/2022 - then it is giving me costs for the period 01/11/2022 to 31/12/2022. I actually want it to give me Zero as there has been no costs posted since 31/12/2022.
The Max Date needs to refer to today as an absolute value. i have tried various things like creating a Column for Today's date in the table but that is not working for me either.
I really need help on this one.
Thanks in advance. 
 
3 REPLIES 3
v-tangjie-msft
Community Support
Community Support

Hi @Sean-OReilly ,

 

Please try this measure.

Costs last 2 months = 
CALCULATE(
    [Total Costs to Date],
    DATESINPERIOD(
        MK_Job_LedgerEntries[Posting_Date],
        TODAY(),
        -2,
        MONTH
    )
)

Best Regards,

Neeko Tang

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

Hi @v-tangjie-msft 

Thanks for coming back to me.

Unfortunatley it did not work. It just comes back Blank

 

Here is what i tried 

Costs last 2 months v2 = calculate ( [Total Costs to Date], DATESINPERIOD(MK_Job_LedgerEntries[Posting_Date], today(), -2, MONTH ))
 
SeanOReilly_0-1688117701483.png

 

 
Sean-OReilly
Helper IV
Helper IV

Should Add A Column instead of Using a Measure ?

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors