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.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Puja_Kumari25 ,

I'm not sure how your dataset is designed, here is my sample.

Creates a disconnected calendar table with date fields derived from the dates of the data table.

Calendar = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

 


The measure is as follows.

ForYearSales = 
VAR SelectedDate = SELECTEDVALUE('Calendar'[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 date is selected, use the selected date as the end of the year
    )
VAR SalesInRange =
    CALCULATE(
        [ActualSales],  -- Calculate the sales measure
       KEEPFILTERS('Table'[Date] >= StartfYear && 'Table'[Date] <= EndfYear)  -- Filter for the selected year or current year
    )

RETURN
    IF(ISBLANK(SelectedDate), --If no date is selected
    IF(
        MAX('Table'[Date])<StartfYear,BLANK(), -- Dates less than the current year are blank.
        IF(ISBLANK(SalesInRange),0,SalesInRange) -- Returns 0 if no sales data was found for the given period
    ), 
    IF(ISBLANK(SalesInRange),0,SalesInRange) -- Return 0 if no sales data is found for the given period
)

 

Results when a date is selected from the calendar.


Result of not selecting a calendar 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.