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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Hasvine2022
Helper I
Helper I

DATESBETWEEN with start using DATESYTD and end date pervious month

Hello Team,

 

I am having an issue where I need to calculate the sum of the budget from the start of the year which is the 1st of January and it updates automatically every month.

My table is as below

Example:

Nov-2021100000
Dec-2021100000
Jan-2022100000
Feb-2022100000
Mar-2022100000
Apr-2022100000
May-2022100000
Jun-2022100000
Jul-2022100000
Aug-2022100000
Sept-2022100000
Oct--2022100000
Nov-2022100000
Dec-2022100000

 

I tried the below measure but it does not work as I have data until Dec-2022:

 

Budget_FYTD_1 = Calculate(Sum(ProductBudgets[Budget]),
(DATESYTD(ProductBudgets[Period].[Month], "31/12")))

 

I tried to use DATESBETWEEN but can't seem to make it work.

For example, today we are the first of August, and I need the sum of my budget from Jan 2022 to July 2022 and on the first of September, I need my budget to be from Jan 2022 to Aug 2022. 

 

Thank you in advance for your help.

 

Kind Regards,

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @Hasvine2022 

please try

Budget_FYTD_1 =
VAR MaxDate =
    EOMONTH ( TODAY (), -1 )
RETURN
    CALCULATE (
        SUM ( ProductBudgets[Budget] ),
        ProductBudgets[Period] <= MaxDate,
        ALL ( ProductBudgets )
    )

View solution in original post

2 REPLIES 2
tamerj1
Super User
Super User

Hi @Hasvine2022 

please try

Budget_FYTD_1 =
VAR MaxDate =
    EOMONTH ( TODAY (), -1 )
RETURN
    CALCULATE (
        SUM ( ProductBudgets[Budget] ),
        ProductBudgets[Period] <= MaxDate,
        ALL ( ProductBudgets )
    )

Thank you tamerj1. Works great. Have an awesome day ahead.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.