Forum Discussion

Puja_Kumari25's avatar
Puja_Kumari25
Icon for Helper III rankHelper III
1 year ago
Solved

Previous months data generated for line chart

Hi  Team,   I am using the below-mentioned dax for year-wise revenue calculation. Here we have a date table  which is used in date slicer where we hide min date to look like the calendar visual.  U...
"DAX " "DAX help" 'Need help' 'DAX'
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Puja_Kumari25  ,

    You only need to modify the first variable in the measure.

     

    VAR SelectedDate = CALCULATE(MAX('CalendarFY'[Date]),ALLSELECTED())

     

    And the measure is as follows.

     

    ForYearSales = 
VAR SelectedDate = CALCULATE(MAX('CalendarFY'[Date]),ALLSELECTED())  -- Get the selected date from the slicer
VAR StartfYear = 
    IF(
        ISBLANK(SelectedDate),  -- Check if no date is selected
        DATE(YEAR(TODAY()), 1, 1),  -- If no date is selected, get the start of the current year (1st January)
        DATE(YEAR(SelectedDate), 1, 1)  -- If a date is selected, get the start of the selected year (1st January)
    )
VAR EndfYear = 
    IF(
        ISBLANK(SelectedDate),  -- Check if no date is selected
        TODAY(),  -- If no date is selected, use today's date as the end of the year
        SelectedDate  -- If date is selected, use the selected date as the end of the year
    )


VAR SalesInRange = 
    CALCULATE(
        [ActualSales],  -- Calculate the sales measure
        'CalendarFY'[Date] >= StartfYear && 'CalendarFY'[Date] <= EndfYear   && 'CalendarFY'[year] >=  YEAR(StartfYear) && 'CalendarFY'[year] <=  YEAR(EndfYear)
    )
RETURN
    IF(ISBLANK(SalesInRange), 0, SalesInRange)  -- Return 0 if no sales data is found for the given period

     


    The final result is as follows. The line chart will be a zero line for the month following the selected date.


    Please see the attached pbix for reference.

    Best Regards,
    Dengliang Li

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

Poojara_D12's avatar
Poojara_D12
Icon for Super User rankSuper User
1 year ago

Hi Puja_Kumari25 

Can you please try this:

ForYearSales =
VAR SelectedDate = MAX('CalendarFY'[Date])  -- Get the selected date from the slicer
VAR StartfYear =
    IF(
        ISBLANK(SelectedDate),  -- Check if no date is selected
        DATE(YEAR(TODAY()), 1, 1),  -- If no date is selected, get the start of the current year (1st January)
        DATE(YEAR(SelectedDate), 1, 1)  -- If a date is selected, get the start of the selected year (1st January)
    )
VAR EndfYear =
    IF(
        ISBLANK(SelectedDate),  -- Check if no date is selected
        TODAY(),  -- If no date is selected, use today's date as the end of the year
        SelectedDate  -- If a date is selected, use the selected date as the end of the year
    )
VAR IsInRange =
    MAX('CalendarFY'[Date]) <= EndfYear  -- Check if the current date in the context is within the selected range
VAR SalesInRange =
    CALCULATE(
        [ActualSales],  -- Calculate the sales measure
        'CalendarFY'[Date] >= StartfYear && 'CalendarFY'[Date] <= EndfYear  -- Filter for the selected year or current year
    )
RETURN
    IF(IsInRange, SalesInRange, 0)  -- Show actual sales for dates in range, otherwise show 0

Power BI's visuals respect the context of the data being displayed. By explicitly setting the value to zero for months outside the range, the line chart will draw a straight line at zero for these months.

 

Did I answer your question? Mark my post as a solution, this will help others!
If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

Kind Regards,
Poojara
Data Analyst | MSBI Developer | Power BI Consultant
Consider Subscribing my YouTube for Beginners/Advance Concepts: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS